1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self) -> t.List[t.Any]:
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    def is_type(self, *dtypes) -> bool:
 242        return self.type is not None and self.type.is_type(*dtypes)
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return t.cast(E, ancestor)
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes who's parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 492            if not type(node) is self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return self._to_s()
 500
 501    def sql(self, dialect: DialectType = None, **opts) -> str:
 502        """
 503        Returns SQL string representation of this tree.
 504
 505        Args:
 506            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 507            opts: other `sqlglot.generator.Generator` options.
 508
 509        Returns:
 510            The SQL string.
 511        """
 512        from sqlglot.dialects import Dialect
 513
 514        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 515
 516    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 517        indent = "" if not level else "\n"
 518        indent += "".join(["  "] * level)
 519        left = f"({self.key.upper()} "
 520
 521        args: t.Dict[str, t.Any] = {
 522            k: ", ".join(
 523                v._to_s(hide_missing=hide_missing, level=level + 1)
 524                if hasattr(v, "_to_s")
 525                else str(v)
 526                for v in ensure_list(vs)
 527                if v is not None
 528            )
 529            for k, vs in self.args.items()
 530        }
 531        args["comments"] = self.comments
 532        args["type"] = self.type
 533        args = {k: v for k, v in args.items() if v or not hide_missing}
 534
 535        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 536        right += ")"
 537
 538        return indent + left + right
 539
 540    def transform(self, fun, *args, copy=True, **kwargs):
 541        """
 542        Recursively visits all tree nodes (excluding already transformed ones)
 543        and applies the given transformation function to each node.
 544
 545        Args:
 546            fun (function): a function which takes a node as an argument and returns a
 547                new transformed node or the same node without modifications. If the function
 548                returns None, then the corresponding node will be removed from the syntax tree.
 549            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 550                modified in place.
 551
 552        Returns:
 553            The transformed tree.
 554        """
 555        node = self.copy() if copy else self
 556        new_node = fun(node, *args, **kwargs)
 557
 558        if new_node is None or not isinstance(new_node, Expression):
 559            return new_node
 560        if new_node is not node:
 561            new_node.parent = node.parent
 562            return new_node
 563
 564        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 565        return new_node
 566
 567    @t.overload
 568    def replace(self, expression: E) -> E:
 569        ...
 570
 571    @t.overload
 572    def replace(self, expression: None) -> None:
 573        ...
 574
 575    def replace(self, expression):
 576        """
 577        Swap out this expression with a new expression.
 578
 579        For example::
 580
 581            >>> tree = Select().select("x").from_("tbl")
 582            >>> tree.find(Column).replace(Column(this="y"))
 583            (COLUMN this: y)
 584            >>> tree.sql()
 585            'SELECT y FROM tbl'
 586
 587        Args:
 588            expression: new node
 589
 590        Returns:
 591            The new expression or expressions.
 592        """
 593        if not self.parent:
 594            return expression
 595
 596        parent = self.parent
 597        self.parent = None
 598
 599        replace_children(parent, lambda child: expression if child is self else child)
 600        return expression
 601
 602    def pop(self: E) -> E:
 603        """
 604        Remove this expression from its AST.
 605
 606        Returns:
 607            The popped expression.
 608        """
 609        self.replace(None)
 610        return self
 611
 612    def assert_is(self, type_: t.Type[E]) -> E:
 613        """
 614        Assert that this `Expression` is an instance of `type_`.
 615
 616        If it is NOT an instance of `type_`, this raises an assertion error.
 617        Otherwise, this returns this expression.
 618
 619        Examples:
 620            This is useful for type security in chained expressions:
 621
 622            >>> import sqlglot
 623            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 624            'SELECT x, z FROM y'
 625        """
 626        assert isinstance(self, type_)
 627        return self
 628
 629    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 630        """
 631        Checks if this expression is valid (e.g. all mandatory args are set).
 632
 633        Args:
 634            args: a sequence of values that were used to instantiate a Func expression. This is used
 635                to check that the provided arguments don't exceed the function argument limit.
 636
 637        Returns:
 638            A list of error messages for all possible errors that were found.
 639        """
 640        errors: t.List[str] = []
 641
 642        for k in self.args:
 643            if k not in self.arg_types:
 644                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 645        for k, mandatory in self.arg_types.items():
 646            v = self.args.get(k)
 647            if mandatory and (v is None or (isinstance(v, list) and not v)):
 648                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 649
 650        if (
 651            args
 652            and isinstance(self, Func)
 653            and len(args) > len(self.arg_types)
 654            and not self.is_var_len_args
 655        ):
 656            errors.append(
 657                f"The number of provided arguments ({len(args)}) is greater than "
 658                f"the maximum number of supported arguments ({len(self.arg_types)})"
 659            )
 660
 661        return errors
 662
 663    def dump(self):
 664        """
 665        Dump this Expression to a JSON-serializable dict.
 666        """
 667        from sqlglot.serde import dump
 668
 669        return dump(self)
 670
 671    @classmethod
 672    def load(cls, obj):
 673        """
 674        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 675        """
 676        from sqlglot.serde import load
 677
 678        return load(obj)
 679
 680    def and_(
 681        self,
 682        *expressions: t.Optional[ExpOrStr],
 683        dialect: DialectType = None,
 684        copy: bool = True,
 685        **opts,
 686    ) -> Condition:
 687        """
 688        AND this condition with one or multiple expressions.
 689
 690        Example:
 691            >>> condition("x=1").and_("y=1").sql()
 692            'x = 1 AND y = 1'
 693
 694        Args:
 695            *expressions: the SQL code strings to parse.
 696                If an `Expression` instance is passed, it will be used as-is.
 697            dialect: the dialect used to parse the input expression.
 698            copy: whether or not to copy the involved expressions (only applies to Expressions).
 699            opts: other options to use to parse the input expressions.
 700
 701        Returns:
 702            The new And condition.
 703        """
 704        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 705
 706    def or_(
 707        self,
 708        *expressions: t.Optional[ExpOrStr],
 709        dialect: DialectType = None,
 710        copy: bool = True,
 711        **opts,
 712    ) -> Condition:
 713        """
 714        OR this condition with one or multiple expressions.
 715
 716        Example:
 717            >>> condition("x=1").or_("y=1").sql()
 718            'x = 1 OR y = 1'
 719
 720        Args:
 721            *expressions: the SQL code strings to parse.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            dialect: the dialect used to parse the input expression.
 724            copy: whether or not to copy the involved expressions (only applies to Expressions).
 725            opts: other options to use to parse the input expressions.
 726
 727        Returns:
 728            The new Or condition.
 729        """
 730        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 731
 732    def not_(self, copy: bool = True):
 733        """
 734        Wrap this condition with NOT.
 735
 736        Example:
 737            >>> condition("x=1").not_().sql()
 738            'NOT x = 1'
 739
 740        Args:
 741            copy: whether or not to copy this object.
 742
 743        Returns:
 744            The new Not instance.
 745        """
 746        return not_(self, copy=copy)
 747
 748    def as_(
 749        self,
 750        alias: str | Identifier,
 751        quoted: t.Optional[bool] = None,
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Alias:
 756        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 757
 758    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 759        this = self.copy()
 760        other = convert(other, copy=True)
 761        if not isinstance(this, klass) and not isinstance(other, klass):
 762            this = _wrap(this, Binary)
 763            other = _wrap(other, Binary)
 764        if reverse:
 765            return klass(this=other, expression=this)
 766        return klass(this=this, expression=other)
 767
 768    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 769        return Bracket(
 770            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 771        )
 772
 773    def __iter__(self) -> t.Iterator:
 774        if "expressions" in self.arg_types:
 775            return iter(self.args.get("expressions") or [])
 776        # We define this because __getitem__ converts Expression into an iterable, which is
 777        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 778        # See: https://peps.python.org/pep-0234/
 779        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 780
 781    def isin(
 782        self,
 783        *expressions: t.Any,
 784        query: t.Optional[ExpOrStr] = None,
 785        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 786        copy: bool = True,
 787        **opts,
 788    ) -> In:
 789        return In(
 790            this=maybe_copy(self, copy),
 791            expressions=[convert(e, copy=copy) for e in expressions],
 792            query=maybe_parse(query, copy=copy, **opts) if query else None,
 793            unnest=Unnest(
 794                expressions=[
 795                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 796                ]
 797            )
 798            if unnest
 799            else None,
 800        )
 801
 802    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 803        return Between(
 804            this=maybe_copy(self, copy),
 805            low=convert(low, copy=copy, **opts),
 806            high=convert(high, copy=copy, **opts),
 807        )
 808
 809    def is_(self, other: ExpOrStr) -> Is:
 810        return self._binop(Is, other)
 811
 812    def like(self, other: ExpOrStr) -> Like:
 813        return self._binop(Like, other)
 814
 815    def ilike(self, other: ExpOrStr) -> ILike:
 816        return self._binop(ILike, other)
 817
 818    def eq(self, other: t.Any) -> EQ:
 819        return self._binop(EQ, other)
 820
 821    def neq(self, other: t.Any) -> NEQ:
 822        return self._binop(NEQ, other)
 823
 824    def rlike(self, other: ExpOrStr) -> RegexpLike:
 825        return self._binop(RegexpLike, other)
 826
 827    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 828        div = self._binop(Div, other)
 829        div.args["typed"] = typed
 830        div.args["safe"] = safe
 831        return div
 832
 833    def __lt__(self, other: t.Any) -> LT:
 834        return self._binop(LT, other)
 835
 836    def __le__(self, other: t.Any) -> LTE:
 837        return self._binop(LTE, other)
 838
 839    def __gt__(self, other: t.Any) -> GT:
 840        return self._binop(GT, other)
 841
 842    def __ge__(self, other: t.Any) -> GTE:
 843        return self._binop(GTE, other)
 844
 845    def __add__(self, other: t.Any) -> Add:
 846        return self._binop(Add, other)
 847
 848    def __radd__(self, other: t.Any) -> Add:
 849        return self._binop(Add, other, reverse=True)
 850
 851    def __sub__(self, other: t.Any) -> Sub:
 852        return self._binop(Sub, other)
 853
 854    def __rsub__(self, other: t.Any) -> Sub:
 855        return self._binop(Sub, other, reverse=True)
 856
 857    def __mul__(self, other: t.Any) -> Mul:
 858        return self._binop(Mul, other)
 859
 860    def __rmul__(self, other: t.Any) -> Mul:
 861        return self._binop(Mul, other, reverse=True)
 862
 863    def __truediv__(self, other: t.Any) -> Div:
 864        return self._binop(Div, other)
 865
 866    def __rtruediv__(self, other: t.Any) -> Div:
 867        return self._binop(Div, other, reverse=True)
 868
 869    def __floordiv__(self, other: t.Any) -> IntDiv:
 870        return self._binop(IntDiv, other)
 871
 872    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 873        return self._binop(IntDiv, other, reverse=True)
 874
 875    def __mod__(self, other: t.Any) -> Mod:
 876        return self._binop(Mod, other)
 877
 878    def __rmod__(self, other: t.Any) -> Mod:
 879        return self._binop(Mod, other, reverse=True)
 880
 881    def __pow__(self, other: t.Any) -> Pow:
 882        return self._binop(Pow, other)
 883
 884    def __rpow__(self, other: t.Any) -> Pow:
 885        return self._binop(Pow, other, reverse=True)
 886
 887    def __and__(self, other: t.Any) -> And:
 888        return self._binop(And, other)
 889
 890    def __rand__(self, other: t.Any) -> And:
 891        return self._binop(And, other, reverse=True)
 892
 893    def __or__(self, other: t.Any) -> Or:
 894        return self._binop(Or, other)
 895
 896    def __ror__(self, other: t.Any) -> Or:
 897        return self._binop(Or, other, reverse=True)
 898
 899    def __neg__(self) -> Neg:
 900        return Neg(this=_wrap(self.copy(), Binary))
 901
 902    def __invert__(self) -> Not:
 903        return not_(self.copy())
 904
 905
 906IntoType = t.Union[
 907    str,
 908    t.Type[Expression],
 909    t.Collection[t.Union[str, t.Type[Expression]]],
 910]
 911ExpOrStr = t.Union[str, Expression]
 912
 913
 914class Condition(Expression):
 915    """Logical conditions like x AND y, or simply x"""
 916
 917
 918class Predicate(Condition):
 919    """Relationships like x = y, x > 1, x >= y."""
 920
 921
 922class DerivedTable(Expression):
 923    @property
 924    def selects(self) -> t.List[Expression]:
 925        return self.this.selects if isinstance(self.this, Subqueryable) else []
 926
 927    @property
 928    def named_selects(self) -> t.List[str]:
 929        return [select.output_name for select in self.selects]
 930
 931
 932class Unionable(Expression):
 933    def union(
 934        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 935    ) -> Unionable:
 936        """
 937        Builds a UNION expression.
 938
 939        Example:
 940            >>> import sqlglot
 941            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 942            'SELECT * FROM foo UNION SELECT * FROM bla'
 943
 944        Args:
 945            expression: the SQL code string.
 946                If an `Expression` instance is passed, it will be used as-is.
 947            distinct: set the DISTINCT flag if and only if this is true.
 948            dialect: the dialect used to parse the input expression.
 949            opts: other options to use to parse the input expressions.
 950
 951        Returns:
 952            The new Union expression.
 953        """
 954        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 955
 956    def intersect(
 957        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 958    ) -> Unionable:
 959        """
 960        Builds an INTERSECT expression.
 961
 962        Example:
 963            >>> import sqlglot
 964            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 965            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 966
 967        Args:
 968            expression: the SQL code string.
 969                If an `Expression` instance is passed, it will be used as-is.
 970            distinct: set the DISTINCT flag if and only if this is true.
 971            dialect: the dialect used to parse the input expression.
 972            opts: other options to use to parse the input expressions.
 973
 974        Returns:
 975            The new Intersect expression.
 976        """
 977        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 978
 979    def except_(
 980        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 981    ) -> Unionable:
 982        """
 983        Builds an EXCEPT expression.
 984
 985        Example:
 986            >>> import sqlglot
 987            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 988            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 989
 990        Args:
 991            expression: the SQL code string.
 992                If an `Expression` instance is passed, it will be used as-is.
 993            distinct: set the DISTINCT flag if and only if this is true.
 994            dialect: the dialect used to parse the input expression.
 995            opts: other options to use to parse the input expressions.
 996
 997        Returns:
 998            The new Except expression.
 999        """
1000        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1001
1002
1003class UDTF(DerivedTable, Unionable):
1004    @property
1005    def selects(self) -> t.List[Expression]:
1006        alias = self.args.get("alias")
1007        return alias.columns if alias else []
1008
1009
1010class Cache(Expression):
1011    arg_types = {
1012        "with": False,
1013        "this": True,
1014        "lazy": False,
1015        "options": False,
1016        "expression": False,
1017    }
1018
1019
1020class Uncache(Expression):
1021    arg_types = {"this": True, "exists": False}
1022
1023
1024class Refresh(Expression):
1025    pass
1026
1027
1028class DDL(Expression):
1029    @property
1030    def ctes(self):
1031        with_ = self.args.get("with")
1032        if not with_:
1033            return []
1034        return with_.expressions
1035
1036    @property
1037    def named_selects(self) -> t.List[str]:
1038        if isinstance(self.expression, Subqueryable):
1039            return self.expression.named_selects
1040        return []
1041
1042    @property
1043    def selects(self) -> t.List[Expression]:
1044        if isinstance(self.expression, Subqueryable):
1045            return self.expression.selects
1046        return []
1047
1048
1049class Create(DDL):
1050    arg_types = {
1051        "with": False,
1052        "this": True,
1053        "kind": True,
1054        "expression": False,
1055        "exists": False,
1056        "properties": False,
1057        "replace": False,
1058        "unique": False,
1059        "indexes": False,
1060        "no_schema_binding": False,
1061        "begin": False,
1062        "end": False,
1063        "clone": False,
1064    }
1065
1066
1067# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1068# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1069# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1070class Clone(Expression):
1071    arg_types = {
1072        "this": True,
1073        "when": False,
1074        "kind": False,
1075        "shallow": False,
1076        "expression": False,
1077        "copy": False,
1078    }
1079
1080
1081class Describe(Expression):
1082    arg_types = {"this": True, "kind": False, "expressions": False}
1083
1084
1085class Kill(Expression):
1086    arg_types = {"this": True, "kind": False}
1087
1088
1089class Pragma(Expression):
1090    pass
1091
1092
1093class Set(Expression):
1094    arg_types = {"expressions": False, "unset": False, "tag": False}
1095
1096
1097class SetItem(Expression):
1098    arg_types = {
1099        "this": False,
1100        "expressions": False,
1101        "kind": False,
1102        "collate": False,  # MySQL SET NAMES statement
1103        "global": False,
1104    }
1105
1106
1107class Show(Expression):
1108    arg_types = {
1109        "this": True,
1110        "target": False,
1111        "offset": False,
1112        "limit": False,
1113        "like": False,
1114        "where": False,
1115        "db": False,
1116        "scope": False,
1117        "scope_kind": False,
1118        "full": False,
1119        "mutex": False,
1120        "query": False,
1121        "channel": False,
1122        "global": False,
1123        "log": False,
1124        "position": False,
1125        "types": False,
1126    }
1127
1128
1129class UserDefinedFunction(Expression):
1130    arg_types = {"this": True, "expressions": False, "wrapped": False}
1131
1132
1133class CharacterSet(Expression):
1134    arg_types = {"this": True, "default": False}
1135
1136
1137class With(Expression):
1138    arg_types = {"expressions": True, "recursive": False}
1139
1140    @property
1141    def recursive(self) -> bool:
1142        return bool(self.args.get("recursive"))
1143
1144
1145class WithinGroup(Expression):
1146    arg_types = {"this": True, "expression": False}
1147
1148
1149class CTE(DerivedTable):
1150    arg_types = {"this": True, "alias": True}
1151
1152
1153class TableAlias(Expression):
1154    arg_types = {"this": False, "columns": False}
1155
1156    @property
1157    def columns(self):
1158        return self.args.get("columns") or []
1159
1160
1161class BitString(Condition):
1162    pass
1163
1164
1165class HexString(Condition):
1166    pass
1167
1168
1169class ByteString(Condition):
1170    pass
1171
1172
1173class RawString(Condition):
1174    pass
1175
1176
1177class Column(Condition):
1178    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1179
1180    @property
1181    def table(self) -> str:
1182        return self.text("table")
1183
1184    @property
1185    def db(self) -> str:
1186        return self.text("db")
1187
1188    @property
1189    def catalog(self) -> str:
1190        return self.text("catalog")
1191
1192    @property
1193    def output_name(self) -> str:
1194        return self.name
1195
1196    @property
1197    def parts(self) -> t.List[Identifier]:
1198        """Return the parts of a column in order catalog, db, table, name."""
1199        return [
1200            t.cast(Identifier, self.args[part])
1201            for part in ("catalog", "db", "table", "this")
1202            if self.args.get(part)
1203        ]
1204
1205    def to_dot(self) -> Dot | Identifier:
1206        """Converts the column into a dot expression."""
1207        parts = self.parts
1208        parent = self.parent
1209
1210        while parent:
1211            if isinstance(parent, Dot):
1212                parts.append(parent.expression)
1213            parent = parent.parent
1214
1215        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1216
1217
1218class ColumnPosition(Expression):
1219    arg_types = {"this": False, "position": True}
1220
1221
1222class ColumnDef(Expression):
1223    arg_types = {
1224        "this": True,
1225        "kind": False,
1226        "constraints": False,
1227        "exists": False,
1228        "position": False,
1229    }
1230
1231    @property
1232    def constraints(self) -> t.List[ColumnConstraint]:
1233        return self.args.get("constraints") or []
1234
1235
1236class AlterColumn(Expression):
1237    arg_types = {
1238        "this": True,
1239        "dtype": False,
1240        "collate": False,
1241        "using": False,
1242        "default": False,
1243        "drop": False,
1244    }
1245
1246
1247class RenameTable(Expression):
1248    pass
1249
1250
1251class SwapTable(Expression):
1252    pass
1253
1254
1255class Comment(Expression):
1256    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1257
1258
1259class Comprehension(Expression):
1260    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1261
1262
1263# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1264class MergeTreeTTLAction(Expression):
1265    arg_types = {
1266        "this": True,
1267        "delete": False,
1268        "recompress": False,
1269        "to_disk": False,
1270        "to_volume": False,
1271    }
1272
1273
1274# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1275class MergeTreeTTL(Expression):
1276    arg_types = {
1277        "expressions": True,
1278        "where": False,
1279        "group": False,
1280        "aggregates": False,
1281    }
1282
1283
1284# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1285class IndexConstraintOption(Expression):
1286    arg_types = {
1287        "key_block_size": False,
1288        "using": False,
1289        "parser": False,
1290        "comment": False,
1291        "visible": False,
1292        "engine_attr": False,
1293        "secondary_engine_attr": False,
1294    }
1295
1296
1297class ColumnConstraint(Expression):
1298    arg_types = {"this": False, "kind": True}
1299
1300    @property
1301    def kind(self) -> ColumnConstraintKind:
1302        return self.args["kind"]
1303
1304
1305class ColumnConstraintKind(Expression):
1306    pass
1307
1308
1309class AutoIncrementColumnConstraint(ColumnConstraintKind):
1310    pass
1311
1312
1313class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1314    arg_types = {"this": True, "expression": True}
1315
1316
1317class CaseSpecificColumnConstraint(ColumnConstraintKind):
1318    arg_types = {"not_": True}
1319
1320
1321class CharacterSetColumnConstraint(ColumnConstraintKind):
1322    arg_types = {"this": True}
1323
1324
1325class CheckColumnConstraint(ColumnConstraintKind):
1326    pass
1327
1328
1329class ClusteredColumnConstraint(ColumnConstraintKind):
1330    pass
1331
1332
1333class CollateColumnConstraint(ColumnConstraintKind):
1334    pass
1335
1336
1337class CommentColumnConstraint(ColumnConstraintKind):
1338    pass
1339
1340
1341class CompressColumnConstraint(ColumnConstraintKind):
1342    pass
1343
1344
1345class DateFormatColumnConstraint(ColumnConstraintKind):
1346    arg_types = {"this": True}
1347
1348
1349class DefaultColumnConstraint(ColumnConstraintKind):
1350    pass
1351
1352
1353class EncodeColumnConstraint(ColumnConstraintKind):
1354    pass
1355
1356
1357class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1358    # this: True -> ALWAYS, this: False -> BY DEFAULT
1359    arg_types = {
1360        "this": False,
1361        "expression": False,
1362        "on_null": False,
1363        "start": False,
1364        "increment": False,
1365        "minvalue": False,
1366        "maxvalue": False,
1367        "cycle": False,
1368    }
1369
1370
1371class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1372    arg_types = {"start": True, "hidden": False}
1373
1374
1375# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1376class IndexColumnConstraint(ColumnConstraintKind):
1377    arg_types = {
1378        "this": False,
1379        "schema": True,
1380        "kind": False,
1381        "index_type": False,
1382        "options": False,
1383    }
1384
1385
1386class InlineLengthColumnConstraint(ColumnConstraintKind):
1387    pass
1388
1389
1390class NonClusteredColumnConstraint(ColumnConstraintKind):
1391    pass
1392
1393
1394class NotForReplicationColumnConstraint(ColumnConstraintKind):
1395    arg_types = {}
1396
1397
1398class NotNullColumnConstraint(ColumnConstraintKind):
1399    arg_types = {"allow_null": False}
1400
1401
1402# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1403class OnUpdateColumnConstraint(ColumnConstraintKind):
1404    pass
1405
1406
1407class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1408    arg_types = {"desc": False}
1409
1410
1411class TitleColumnConstraint(ColumnConstraintKind):
1412    pass
1413
1414
1415class UniqueColumnConstraint(ColumnConstraintKind):
1416    arg_types = {"this": False, "index_type": False}
1417
1418
1419class UppercaseColumnConstraint(ColumnConstraintKind):
1420    arg_types: t.Dict[str, t.Any] = {}
1421
1422
1423class PathColumnConstraint(ColumnConstraintKind):
1424    pass
1425
1426
1427# computed column expression
1428# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1429class ComputedColumnConstraint(ColumnConstraintKind):
1430    arg_types = {"this": True, "persisted": False, "not_null": False}
1431
1432
1433class Constraint(Expression):
1434    arg_types = {"this": True, "expressions": True}
1435
1436
1437class Delete(Expression):
1438    arg_types = {
1439        "with": False,
1440        "this": False,
1441        "using": False,
1442        "where": False,
1443        "returning": False,
1444        "limit": False,
1445        "tables": False,  # Multiple-Table Syntax (MySQL)
1446    }
1447
1448    def delete(
1449        self,
1450        table: ExpOrStr,
1451        dialect: DialectType = None,
1452        copy: bool = True,
1453        **opts,
1454    ) -> Delete:
1455        """
1456        Create a DELETE expression or replace the table on an existing DELETE expression.
1457
1458        Example:
1459            >>> delete("tbl").sql()
1460            'DELETE FROM tbl'
1461
1462        Args:
1463            table: the table from which to delete.
1464            dialect: the dialect used to parse the input expression.
1465            copy: if `False`, modify this expression instance in-place.
1466            opts: other options to use to parse the input expressions.
1467
1468        Returns:
1469            Delete: the modified expression.
1470        """
1471        return _apply_builder(
1472            expression=table,
1473            instance=self,
1474            arg="this",
1475            dialect=dialect,
1476            into=Table,
1477            copy=copy,
1478            **opts,
1479        )
1480
1481    def where(
1482        self,
1483        *expressions: t.Optional[ExpOrStr],
1484        append: bool = True,
1485        dialect: DialectType = None,
1486        copy: bool = True,
1487        **opts,
1488    ) -> Delete:
1489        """
1490        Append to or set the WHERE expressions.
1491
1492        Example:
1493            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1494            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1495
1496        Args:
1497            *expressions: the SQL code strings to parse.
1498                If an `Expression` instance is passed, it will be used as-is.
1499                Multiple expressions are combined with an AND operator.
1500            append: if `True`, AND the new expressions to any existing expression.
1501                Otherwise, this resets the expression.
1502            dialect: the dialect used to parse the input expressions.
1503            copy: if `False`, modify this expression instance in-place.
1504            opts: other options to use to parse the input expressions.
1505
1506        Returns:
1507            Delete: the modified expression.
1508        """
1509        return _apply_conjunction_builder(
1510            *expressions,
1511            instance=self,
1512            arg="where",
1513            append=append,
1514            into=Where,
1515            dialect=dialect,
1516            copy=copy,
1517            **opts,
1518        )
1519
1520    def returning(
1521        self,
1522        expression: ExpOrStr,
1523        dialect: DialectType = None,
1524        copy: bool = True,
1525        **opts,
1526    ) -> Delete:
1527        """
1528        Set the RETURNING expression. Not supported by all dialects.
1529
1530        Example:
1531            >>> delete("tbl").returning("*", dialect="postgres").sql()
1532            'DELETE FROM tbl RETURNING *'
1533
1534        Args:
1535            expression: the SQL code strings to parse.
1536                If an `Expression` instance is passed, it will be used as-is.
1537            dialect: the dialect used to parse the input expressions.
1538            copy: if `False`, modify this expression instance in-place.
1539            opts: other options to use to parse the input expressions.
1540
1541        Returns:
1542            Delete: the modified expression.
1543        """
1544        return _apply_builder(
1545            expression=expression,
1546            instance=self,
1547            arg="returning",
1548            prefix="RETURNING",
1549            dialect=dialect,
1550            copy=copy,
1551            into=Returning,
1552            **opts,
1553        )
1554
1555
1556class Drop(Expression):
1557    arg_types = {
1558        "this": False,
1559        "kind": False,
1560        "exists": False,
1561        "temporary": False,
1562        "materialized": False,
1563        "cascade": False,
1564        "constraints": False,
1565        "purge": False,
1566    }
1567
1568
1569class Filter(Expression):
1570    arg_types = {"this": True, "expression": True}
1571
1572
1573class Check(Expression):
1574    pass
1575
1576
1577# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1578class Connect(Expression):
1579    arg_types = {"start": False, "connect": True}
1580
1581
1582class Prior(Expression):
1583    pass
1584
1585
1586class Directory(Expression):
1587    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1588    arg_types = {"this": True, "local": False, "row_format": False}
1589
1590
1591class ForeignKey(Expression):
1592    arg_types = {
1593        "expressions": True,
1594        "reference": False,
1595        "delete": False,
1596        "update": False,
1597    }
1598
1599
1600class ColumnPrefix(Expression):
1601    arg_types = {"this": True, "expression": True}
1602
1603
1604class PrimaryKey(Expression):
1605    arg_types = {"expressions": True, "options": False}
1606
1607
1608# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1609# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1610class Into(Expression):
1611    arg_types = {"this": True, "temporary": False, "unlogged": False}
1612
1613
1614class From(Expression):
1615    @property
1616    def name(self) -> str:
1617        return self.this.name
1618
1619    @property
1620    def alias_or_name(self) -> str:
1621        return self.this.alias_or_name
1622
1623
1624class Having(Expression):
1625    pass
1626
1627
1628class Hint(Expression):
1629    arg_types = {"expressions": True}
1630
1631
1632class JoinHint(Expression):
1633    arg_types = {"this": True, "expressions": True}
1634
1635
1636class Identifier(Expression):
1637    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1638
1639    @property
1640    def quoted(self) -> bool:
1641        return bool(self.args.get("quoted"))
1642
1643    @property
1644    def hashable_args(self) -> t.Any:
1645        return (self.this, self.quoted)
1646
1647    @property
1648    def output_name(self) -> str:
1649        return self.name
1650
1651
1652# https://www.postgresql.org/docs/current/indexes-opclass.html
1653class Opclass(Expression):
1654    arg_types = {"this": True, "expression": True}
1655
1656
1657class Index(Expression):
1658    arg_types = {
1659        "this": False,
1660        "table": False,
1661        "using": False,
1662        "where": False,
1663        "columns": False,
1664        "unique": False,
1665        "primary": False,
1666        "amp": False,  # teradata
1667        "partition_by": False,  # teradata
1668        "where": False,  # postgres partial indexes
1669    }
1670
1671
1672class Insert(DDL):
1673    arg_types = {
1674        "with": False,
1675        "this": True,
1676        "expression": False,
1677        "conflict": False,
1678        "returning": False,
1679        "overwrite": False,
1680        "exists": False,
1681        "partition": False,
1682        "alternative": False,
1683        "where": False,
1684        "ignore": False,
1685        "by_name": False,
1686    }
1687
1688    def with_(
1689        self,
1690        alias: ExpOrStr,
1691        as_: ExpOrStr,
1692        recursive: t.Optional[bool] = None,
1693        append: bool = True,
1694        dialect: DialectType = None,
1695        copy: bool = True,
1696        **opts,
1697    ) -> Insert:
1698        """
1699        Append to or set the common table expressions.
1700
1701        Example:
1702            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1703            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1704
1705        Args:
1706            alias: the SQL code string to parse as the table name.
1707                If an `Expression` instance is passed, this is used as-is.
1708            as_: the SQL code string to parse as the table expression.
1709                If an `Expression` instance is passed, it will be used as-is.
1710            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1711            append: if `True`, add to any existing expressions.
1712                Otherwise, this resets the expressions.
1713            dialect: the dialect used to parse the input expression.
1714            copy: if `False`, modify this expression instance in-place.
1715            opts: other options to use to parse the input expressions.
1716
1717        Returns:
1718            The modified expression.
1719        """
1720        return _apply_cte_builder(
1721            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1722        )
1723
1724
1725class OnConflict(Expression):
1726    arg_types = {
1727        "duplicate": False,
1728        "expressions": False,
1729        "nothing": False,
1730        "key": False,
1731        "constraint": False,
1732    }
1733
1734
1735class Returning(Expression):
1736    arg_types = {"expressions": True, "into": False}
1737
1738
1739# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1740class Introducer(Expression):
1741    arg_types = {"this": True, "expression": True}
1742
1743
1744# national char, like n'utf8'
1745class National(Expression):
1746    pass
1747
1748
1749class LoadData(Expression):
1750    arg_types = {
1751        "this": True,
1752        "local": False,
1753        "overwrite": False,
1754        "inpath": True,
1755        "partition": False,
1756        "input_format": False,
1757        "serde": False,
1758    }
1759
1760
1761class Partition(Expression):
1762    arg_types = {"expressions": True}
1763
1764
1765class Fetch(Expression):
1766    arg_types = {
1767        "direction": False,
1768        "count": False,
1769        "percent": False,
1770        "with_ties": False,
1771    }
1772
1773
1774class Group(Expression):
1775    arg_types = {
1776        "expressions": False,
1777        "grouping_sets": False,
1778        "cube": False,
1779        "rollup": False,
1780        "totals": False,
1781        "all": False,
1782    }
1783
1784
1785class Lambda(Expression):
1786    arg_types = {"this": True, "expressions": True}
1787
1788
1789class Limit(Expression):
1790    arg_types = {"this": False, "expression": True, "offset": False}
1791
1792
1793class Literal(Condition):
1794    arg_types = {"this": True, "is_string": True}
1795
1796    @property
1797    def hashable_args(self) -> t.Any:
1798        return (self.this, self.args.get("is_string"))
1799
1800    @classmethod
1801    def number(cls, number) -> Literal:
1802        return cls(this=str(number), is_string=False)
1803
1804    @classmethod
1805    def string(cls, string) -> Literal:
1806        return cls(this=str(string), is_string=True)
1807
1808    @property
1809    def output_name(self) -> str:
1810        return self.name
1811
1812
1813class Join(Expression):
1814    arg_types = {
1815        "this": True,
1816        "on": False,
1817        "side": False,
1818        "kind": False,
1819        "using": False,
1820        "method": False,
1821        "global": False,
1822        "hint": False,
1823    }
1824
1825    @property
1826    def method(self) -> str:
1827        return self.text("method").upper()
1828
1829    @property
1830    def kind(self) -> str:
1831        return self.text("kind").upper()
1832
1833    @property
1834    def side(self) -> str:
1835        return self.text("side").upper()
1836
1837    @property
1838    def hint(self) -> str:
1839        return self.text("hint").upper()
1840
1841    @property
1842    def alias_or_name(self) -> str:
1843        return self.this.alias_or_name
1844
1845    def on(
1846        self,
1847        *expressions: t.Optional[ExpOrStr],
1848        append: bool = True,
1849        dialect: DialectType = None,
1850        copy: bool = True,
1851        **opts,
1852    ) -> Join:
1853        """
1854        Append to or set the ON expressions.
1855
1856        Example:
1857            >>> import sqlglot
1858            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1859            'JOIN x ON y = 1'
1860
1861        Args:
1862            *expressions: the SQL code strings to parse.
1863                If an `Expression` instance is passed, it will be used as-is.
1864                Multiple expressions are combined with an AND operator.
1865            append: if `True`, AND the new expressions to any existing expression.
1866                Otherwise, this resets the expression.
1867            dialect: the dialect used to parse the input expressions.
1868            copy: if `False`, modify this expression instance in-place.
1869            opts: other options to use to parse the input expressions.
1870
1871        Returns:
1872            The modified Join expression.
1873        """
1874        join = _apply_conjunction_builder(
1875            *expressions,
1876            instance=self,
1877            arg="on",
1878            append=append,
1879            dialect=dialect,
1880            copy=copy,
1881            **opts,
1882        )
1883
1884        if join.kind == "CROSS":
1885            join.set("kind", None)
1886
1887        return join
1888
1889    def using(
1890        self,
1891        *expressions: t.Optional[ExpOrStr],
1892        append: bool = True,
1893        dialect: DialectType = None,
1894        copy: bool = True,
1895        **opts,
1896    ) -> Join:
1897        """
1898        Append to or set the USING expressions.
1899
1900        Example:
1901            >>> import sqlglot
1902            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1903            'JOIN x USING (foo, bla)'
1904
1905        Args:
1906            *expressions: the SQL code strings to parse.
1907                If an `Expression` instance is passed, it will be used as-is.
1908            append: if `True`, concatenate the new expressions to the existing "using" list.
1909                Otherwise, this resets the expression.
1910            dialect: the dialect used to parse the input expressions.
1911            copy: if `False`, modify this expression instance in-place.
1912            opts: other options to use to parse the input expressions.
1913
1914        Returns:
1915            The modified Join expression.
1916        """
1917        join = _apply_list_builder(
1918            *expressions,
1919            instance=self,
1920            arg="using",
1921            append=append,
1922            dialect=dialect,
1923            copy=copy,
1924            **opts,
1925        )
1926
1927        if join.kind == "CROSS":
1928            join.set("kind", None)
1929
1930        return join
1931
1932
1933class Lateral(UDTF):
1934    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1935
1936
1937class MatchRecognize(Expression):
1938    arg_types = {
1939        "partition_by": False,
1940        "order": False,
1941        "measures": False,
1942        "rows": False,
1943        "after": False,
1944        "pattern": False,
1945        "define": False,
1946        "alias": False,
1947    }
1948
1949
1950# Clickhouse FROM FINAL modifier
1951# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1952class Final(Expression):
1953    pass
1954
1955
1956class Offset(Expression):
1957    arg_types = {"this": False, "expression": True}
1958
1959
1960class Order(Expression):
1961    arg_types = {"this": False, "expressions": True}
1962
1963
1964# hive specific sorts
1965# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1966class Cluster(Order):
1967    pass
1968
1969
1970class Distribute(Order):
1971    pass
1972
1973
1974class Sort(Order):
1975    pass
1976
1977
1978class Ordered(Expression):
1979    arg_types = {"this": True, "desc": False, "nulls_first": True}
1980
1981
1982class Property(Expression):
1983    arg_types = {"this": True, "value": True}
1984
1985
1986class AlgorithmProperty(Property):
1987    arg_types = {"this": True}
1988
1989
1990class AutoIncrementProperty(Property):
1991    arg_types = {"this": True}
1992
1993
1994class BlockCompressionProperty(Property):
1995    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1996
1997
1998class CharacterSetProperty(Property):
1999    arg_types = {"this": True, "default": True}
2000
2001
2002class ChecksumProperty(Property):
2003    arg_types = {"on": False, "default": False}
2004
2005
2006class CollateProperty(Property):
2007    arg_types = {"this": True, "default": False}
2008
2009
2010class CopyGrantsProperty(Property):
2011    arg_types = {}
2012
2013
2014class DataBlocksizeProperty(Property):
2015    arg_types = {
2016        "size": False,
2017        "units": False,
2018        "minimum": False,
2019        "maximum": False,
2020        "default": False,
2021    }
2022
2023
2024class DefinerProperty(Property):
2025    arg_types = {"this": True}
2026
2027
2028class DistKeyProperty(Property):
2029    arg_types = {"this": True}
2030
2031
2032class DistStyleProperty(Property):
2033    arg_types = {"this": True}
2034
2035
2036class EngineProperty(Property):
2037    arg_types = {"this": True}
2038
2039
2040class HeapProperty(Property):
2041    arg_types = {}
2042
2043
2044class ToTableProperty(Property):
2045    arg_types = {"this": True}
2046
2047
2048class ExecuteAsProperty(Property):
2049    arg_types = {"this": True}
2050
2051
2052class ExternalProperty(Property):
2053    arg_types = {"this": False}
2054
2055
2056class FallbackProperty(Property):
2057    arg_types = {"no": True, "protection": False}
2058
2059
2060class FileFormatProperty(Property):
2061    arg_types = {"this": True}
2062
2063
2064class FreespaceProperty(Property):
2065    arg_types = {"this": True, "percent": False}
2066
2067
2068class InputModelProperty(Property):
2069    arg_types = {"this": True}
2070
2071
2072class OutputModelProperty(Property):
2073    arg_types = {"this": True}
2074
2075
2076class IsolatedLoadingProperty(Property):
2077    arg_types = {
2078        "no": True,
2079        "concurrent": True,
2080        "for_all": True,
2081        "for_insert": True,
2082        "for_none": True,
2083    }
2084
2085
2086class JournalProperty(Property):
2087    arg_types = {
2088        "no": False,
2089        "dual": False,
2090        "before": False,
2091        "local": False,
2092        "after": False,
2093    }
2094
2095
2096class LanguageProperty(Property):
2097    arg_types = {"this": True}
2098
2099
2100# spark ddl
2101class ClusteredByProperty(Property):
2102    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2103
2104
2105class DictProperty(Property):
2106    arg_types = {"this": True, "kind": True, "settings": False}
2107
2108
2109class DictSubProperty(Property):
2110    pass
2111
2112
2113class DictRange(Property):
2114    arg_types = {"this": True, "min": True, "max": True}
2115
2116
2117# Clickhouse CREATE ... ON CLUSTER modifier
2118# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2119class OnCluster(Property):
2120    arg_types = {"this": True}
2121
2122
2123class LikeProperty(Property):
2124    arg_types = {"this": True, "expressions": False}
2125
2126
2127class LocationProperty(Property):
2128    arg_types = {"this": True}
2129
2130
2131class LockingProperty(Property):
2132    arg_types = {
2133        "this": False,
2134        "kind": True,
2135        "for_or_in": False,
2136        "lock_type": True,
2137        "override": False,
2138    }
2139
2140
2141class LogProperty(Property):
2142    arg_types = {"no": True}
2143
2144
2145class MaterializedProperty(Property):
2146    arg_types = {"this": False}
2147
2148
2149class MergeBlockRatioProperty(Property):
2150    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2151
2152
2153class NoPrimaryIndexProperty(Property):
2154    arg_types = {}
2155
2156
2157class OnProperty(Property):
2158    arg_types = {"this": True}
2159
2160
2161class OnCommitProperty(Property):
2162    arg_types = {"delete": False}
2163
2164
2165class PartitionedByProperty(Property):
2166    arg_types = {"this": True}
2167
2168
2169# https://www.postgresql.org/docs/current/sql-createtable.html
2170class PartitionBoundSpec(Expression):
2171    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2172    arg_types = {
2173        "this": False,
2174        "expression": False,
2175        "from_expressions": False,
2176        "to_expressions": False,
2177    }
2178
2179
2180class PartitionedOfProperty(Property):
2181    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2182    arg_types = {"this": True, "expression": True}
2183
2184
2185class RemoteWithConnectionModelProperty(Property):
2186    arg_types = {"this": True}
2187
2188
2189class ReturnsProperty(Property):
2190    arg_types = {"this": True, "is_table": False, "table": False}
2191
2192
2193class RowFormatProperty(Property):
2194    arg_types = {"this": True}
2195
2196
2197class RowFormatDelimitedProperty(Property):
2198    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2199    arg_types = {
2200        "fields": False,
2201        "escaped": False,
2202        "collection_items": False,
2203        "map_keys": False,
2204        "lines": False,
2205        "null": False,
2206        "serde": False,
2207    }
2208
2209
2210class RowFormatSerdeProperty(Property):
2211    arg_types = {"this": True, "serde_properties": False}
2212
2213
2214# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2215class QueryTransform(Expression):
2216    arg_types = {
2217        "expressions": True,
2218        "command_script": True,
2219        "schema": False,
2220        "row_format_before": False,
2221        "record_writer": False,
2222        "row_format_after": False,
2223        "record_reader": False,
2224    }
2225
2226
2227class SampleProperty(Property):
2228    arg_types = {"this": True}
2229
2230
2231class SchemaCommentProperty(Property):
2232    arg_types = {"this": True}
2233
2234
2235class SerdeProperties(Property):
2236    arg_types = {"expressions": True}
2237
2238
2239class SetProperty(Property):
2240    arg_types = {"multi": True}
2241
2242
2243class SettingsProperty(Property):
2244    arg_types = {"expressions": True}
2245
2246
2247class SortKeyProperty(Property):
2248    arg_types = {"this": True, "compound": False}
2249
2250
2251class SqlSecurityProperty(Property):
2252    arg_types = {"definer": True}
2253
2254
2255class StabilityProperty(Property):
2256    arg_types = {"this": True}
2257
2258
2259class TemporaryProperty(Property):
2260    arg_types = {}
2261
2262
2263class TransformModelProperty(Property):
2264    arg_types = {"expressions": True}
2265
2266
2267class TransientProperty(Property):
2268    arg_types = {"this": False}
2269
2270
2271class VolatileProperty(Property):
2272    arg_types = {"this": False}
2273
2274
2275class WithDataProperty(Property):
2276    arg_types = {"no": True, "statistics": False}
2277
2278
2279class WithJournalTableProperty(Property):
2280    arg_types = {"this": True}
2281
2282
2283class WithSystemVersioningProperty(Property):
2284    # this -> history table name, expression -> data consistency check
2285    arg_types = {"this": False, "expression": False}
2286
2287
2288class Properties(Expression):
2289    arg_types = {"expressions": True}
2290
2291    NAME_TO_PROPERTY = {
2292        "ALGORITHM": AlgorithmProperty,
2293        "AUTO_INCREMENT": AutoIncrementProperty,
2294        "CHARACTER SET": CharacterSetProperty,
2295        "CLUSTERED_BY": ClusteredByProperty,
2296        "COLLATE": CollateProperty,
2297        "COMMENT": SchemaCommentProperty,
2298        "DEFINER": DefinerProperty,
2299        "DISTKEY": DistKeyProperty,
2300        "DISTSTYLE": DistStyleProperty,
2301        "ENGINE": EngineProperty,
2302        "EXECUTE AS": ExecuteAsProperty,
2303        "FORMAT": FileFormatProperty,
2304        "LANGUAGE": LanguageProperty,
2305        "LOCATION": LocationProperty,
2306        "PARTITIONED_BY": PartitionedByProperty,
2307        "RETURNS": ReturnsProperty,
2308        "ROW_FORMAT": RowFormatProperty,
2309        "SORTKEY": SortKeyProperty,
2310    }
2311
2312    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2313
2314    # CREATE property locations
2315    # Form: schema specified
2316    #   create [POST_CREATE]
2317    #     table a [POST_NAME]
2318    #     (b int) [POST_SCHEMA]
2319    #     with ([POST_WITH])
2320    #     index (b) [POST_INDEX]
2321    #
2322    # Form: alias selection
2323    #   create [POST_CREATE]
2324    #     table a [POST_NAME]
2325    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2326    #     index (c) [POST_INDEX]
2327    class Location(AutoName):
2328        POST_CREATE = auto()
2329        POST_NAME = auto()
2330        POST_SCHEMA = auto()
2331        POST_WITH = auto()
2332        POST_ALIAS = auto()
2333        POST_EXPRESSION = auto()
2334        POST_INDEX = auto()
2335        UNSUPPORTED = auto()
2336
2337    @classmethod
2338    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2339        expressions = []
2340        for key, value in properties_dict.items():
2341            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2342            if property_cls:
2343                expressions.append(property_cls(this=convert(value)))
2344            else:
2345                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2346
2347        return cls(expressions=expressions)
2348
2349
2350class Qualify(Expression):
2351    pass
2352
2353
2354class InputOutputFormat(Expression):
2355    arg_types = {"input_format": False, "output_format": False}
2356
2357
2358# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2359class Return(Expression):
2360    pass
2361
2362
2363class Reference(Expression):
2364    arg_types = {"this": True, "expressions": False, "options": False}
2365
2366
2367class Tuple(Expression):
2368    arg_types = {"expressions": False}
2369
2370    def isin(
2371        self,
2372        *expressions: t.Any,
2373        query: t.Optional[ExpOrStr] = None,
2374        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2375        copy: bool = True,
2376        **opts,
2377    ) -> In:
2378        return In(
2379            this=maybe_copy(self, copy),
2380            expressions=[convert(e, copy=copy) for e in expressions],
2381            query=maybe_parse(query, copy=copy, **opts) if query else None,
2382            unnest=Unnest(
2383                expressions=[
2384                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2385                ]
2386            )
2387            if unnest
2388            else None,
2389        )
2390
2391
2392class Subqueryable(Unionable):
2393    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2394        """
2395        Convert this expression to an aliased expression that can be used as a Subquery.
2396
2397        Example:
2398            >>> subquery = Select().select("x").from_("tbl").subquery()
2399            >>> Select().select("x").from_(subquery).sql()
2400            'SELECT x FROM (SELECT x FROM tbl)'
2401
2402        Args:
2403            alias (str | Identifier): an optional alias for the subquery
2404            copy (bool): if `False`, modify this expression instance in-place.
2405
2406        Returns:
2407            Alias: the subquery
2408        """
2409        instance = maybe_copy(self, copy)
2410        if not isinstance(alias, Expression):
2411            alias = TableAlias(this=to_identifier(alias)) if alias else None
2412
2413        return Subquery(this=instance, alias=alias)
2414
2415    def limit(
2416        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2417    ) -> Select:
2418        raise NotImplementedError
2419
2420    @property
2421    def ctes(self):
2422        with_ = self.args.get("with")
2423        if not with_:
2424            return []
2425        return with_.expressions
2426
2427    @property
2428    def selects(self) -> t.List[Expression]:
2429        raise NotImplementedError("Subqueryable objects must implement `selects`")
2430
2431    @property
2432    def named_selects(self) -> t.List[str]:
2433        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2434
2435    def select(
2436        self,
2437        *expressions: t.Optional[ExpOrStr],
2438        append: bool = True,
2439        dialect: DialectType = None,
2440        copy: bool = True,
2441        **opts,
2442    ) -> Subqueryable:
2443        raise NotImplementedError("Subqueryable objects must implement `select`")
2444
2445    def with_(
2446        self,
2447        alias: ExpOrStr,
2448        as_: ExpOrStr,
2449        recursive: t.Optional[bool] = None,
2450        append: bool = True,
2451        dialect: DialectType = None,
2452        copy: bool = True,
2453        **opts,
2454    ) -> Subqueryable:
2455        """
2456        Append to or set the common table expressions.
2457
2458        Example:
2459            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2460            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2461
2462        Args:
2463            alias: the SQL code string to parse as the table name.
2464                If an `Expression` instance is passed, this is used as-is.
2465            as_: the SQL code string to parse as the table expression.
2466                If an `Expression` instance is passed, it will be used as-is.
2467            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2468            append: if `True`, add to any existing expressions.
2469                Otherwise, this resets the expressions.
2470            dialect: the dialect used to parse the input expression.
2471            copy: if `False`, modify this expression instance in-place.
2472            opts: other options to use to parse the input expressions.
2473
2474        Returns:
2475            The modified expression.
2476        """
2477        return _apply_cte_builder(
2478            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2479        )
2480
2481
2482QUERY_MODIFIERS = {
2483    "match": False,
2484    "laterals": False,
2485    "joins": False,
2486    "connect": False,
2487    "pivots": False,
2488    "where": False,
2489    "group": False,
2490    "having": False,
2491    "qualify": False,
2492    "windows": False,
2493    "distribute": False,
2494    "sort": False,
2495    "cluster": False,
2496    "order": False,
2497    "limit": False,
2498    "offset": False,
2499    "locks": False,
2500    "sample": False,
2501    "settings": False,
2502    "format": False,
2503}
2504
2505
2506# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2507class WithTableHint(Expression):
2508    arg_types = {"expressions": True}
2509
2510
2511# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2512class IndexTableHint(Expression):
2513    arg_types = {"this": True, "expressions": False, "target": False}
2514
2515
2516class Table(Expression):
2517    arg_types = {
2518        "this": True,
2519        "alias": False,
2520        "db": False,
2521        "catalog": False,
2522        "laterals": False,
2523        "joins": False,
2524        "pivots": False,
2525        "hints": False,
2526        "system_time": False,
2527        "version": False,
2528        "format": False,
2529        "pattern": False,
2530        "index": False,
2531        "ordinality": False,
2532    }
2533
2534    @property
2535    def name(self) -> str:
2536        if isinstance(self.this, Func):
2537            return ""
2538        return self.this.name
2539
2540    @property
2541    def db(self) -> str:
2542        return self.text("db")
2543
2544    @property
2545    def catalog(self) -> str:
2546        return self.text("catalog")
2547
2548    @property
2549    def selects(self) -> t.List[Expression]:
2550        return []
2551
2552    @property
2553    def named_selects(self) -> t.List[str]:
2554        return []
2555
2556    @property
2557    def parts(self) -> t.List[Expression]:
2558        """Return the parts of a table in order catalog, db, table."""
2559        parts: t.List[Expression] = []
2560
2561        for arg in ("catalog", "db", "this"):
2562            part = self.args.get(arg)
2563
2564            if isinstance(part, Dot):
2565                parts.extend(part.flatten())
2566            elif isinstance(part, Expression):
2567                parts.append(part)
2568
2569        return parts
2570
2571
2572class Union(Subqueryable):
2573    arg_types = {
2574        "with": False,
2575        "this": True,
2576        "expression": True,
2577        "distinct": False,
2578        "by_name": False,
2579        **QUERY_MODIFIERS,
2580    }
2581
2582    def limit(
2583        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2584    ) -> Select:
2585        """
2586        Set the LIMIT expression.
2587
2588        Example:
2589            >>> select("1").union(select("1")).limit(1).sql()
2590            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2591
2592        Args:
2593            expression: the SQL code string to parse.
2594                This can also be an integer.
2595                If a `Limit` instance is passed, this is used as-is.
2596                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2597            dialect: the dialect used to parse the input expression.
2598            copy: if `False`, modify this expression instance in-place.
2599            opts: other options to use to parse the input expressions.
2600
2601        Returns:
2602            The limited subqueryable.
2603        """
2604        return (
2605            select("*")
2606            .from_(self.subquery(alias="_l_0", copy=copy))
2607            .limit(expression, dialect=dialect, copy=False, **opts)
2608        )
2609
2610    def select(
2611        self,
2612        *expressions: t.Optional[ExpOrStr],
2613        append: bool = True,
2614        dialect: DialectType = None,
2615        copy: bool = True,
2616        **opts,
2617    ) -> Union:
2618        """Append to or set the SELECT of the union recursively.
2619
2620        Example:
2621            >>> from sqlglot import parse_one
2622            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2623            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2624
2625        Args:
2626            *expressions: the SQL code strings to parse.
2627                If an `Expression` instance is passed, it will be used as-is.
2628            append: if `True`, add to any existing expressions.
2629                Otherwise, this resets the expressions.
2630            dialect: the dialect used to parse the input expressions.
2631            copy: if `False`, modify this expression instance in-place.
2632            opts: other options to use to parse the input expressions.
2633
2634        Returns:
2635            Union: the modified expression.
2636        """
2637        this = self.copy() if copy else self
2638        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2639        this.expression.unnest().select(
2640            *expressions, append=append, dialect=dialect, copy=False, **opts
2641        )
2642        return this
2643
2644    @property
2645    def named_selects(self) -> t.List[str]:
2646        return self.this.unnest().named_selects
2647
2648    @property
2649    def is_star(self) -> bool:
2650        return self.this.is_star or self.expression.is_star
2651
2652    @property
2653    def selects(self) -> t.List[Expression]:
2654        return self.this.unnest().selects
2655
2656    @property
2657    def left(self) -> Expression:
2658        return self.this
2659
2660    @property
2661    def right(self) -> Expression:
2662        return self.expression
2663
2664
2665class Except(Union):
2666    pass
2667
2668
2669class Intersect(Union):
2670    pass
2671
2672
2673class Unnest(UDTF):
2674    arg_types = {
2675        "expressions": True,
2676        "alias": False,
2677        "offset": False,
2678    }
2679
2680
2681class Update(Expression):
2682    arg_types = {
2683        "with": False,
2684        "this": False,
2685        "expressions": True,
2686        "from": False,
2687        "where": False,
2688        "returning": False,
2689        "order": False,
2690        "limit": False,
2691    }
2692
2693
2694class Values(UDTF):
2695    arg_types = {"expressions": True, "alias": False}
2696
2697
2698class Var(Expression):
2699    pass
2700
2701
2702class Version(Expression):
2703    """
2704    Time travel, iceberg, bigquery etc
2705    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2706    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2707    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2708    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2709    this is either TIMESTAMP or VERSION
2710    kind is ("AS OF", "BETWEEN")
2711    """
2712
2713    arg_types = {"this": True, "kind": True, "expression": False}
2714
2715
2716class Schema(Expression):
2717    arg_types = {"this": False, "expressions": False}
2718
2719
2720# https://dev.mysql.com/doc/refman/8.0/en/select.html
2721# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2722class Lock(Expression):
2723    arg_types = {"update": True, "expressions": False, "wait": False}
2724
2725
2726class Select(Subqueryable):
2727    arg_types = {
2728        "with": False,
2729        "kind": False,
2730        "expressions": False,
2731        "hint": False,
2732        "distinct": False,
2733        "into": False,
2734        "from": False,
2735        **QUERY_MODIFIERS,
2736    }
2737
2738    def from_(
2739        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2740    ) -> Select:
2741        """
2742        Set the FROM expression.
2743
2744        Example:
2745            >>> Select().from_("tbl").select("x").sql()
2746            'SELECT x FROM tbl'
2747
2748        Args:
2749            expression : the SQL code strings to parse.
2750                If a `From` instance is passed, this is used as-is.
2751                If another `Expression` instance is passed, it will be wrapped in a `From`.
2752            dialect: the dialect used to parse the input expression.
2753            copy: if `False`, modify this expression instance in-place.
2754            opts: other options to use to parse the input expressions.
2755
2756        Returns:
2757            The modified Select expression.
2758        """
2759        return _apply_builder(
2760            expression=expression,
2761            instance=self,
2762            arg="from",
2763            into=From,
2764            prefix="FROM",
2765            dialect=dialect,
2766            copy=copy,
2767            **opts,
2768        )
2769
2770    def group_by(
2771        self,
2772        *expressions: t.Optional[ExpOrStr],
2773        append: bool = True,
2774        dialect: DialectType = None,
2775        copy: bool = True,
2776        **opts,
2777    ) -> Select:
2778        """
2779        Set the GROUP BY expression.
2780
2781        Example:
2782            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2783            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2784
2785        Args:
2786            *expressions: the SQL code strings to parse.
2787                If a `Group` instance is passed, this is used as-is.
2788                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2789                If nothing is passed in then a group by is not applied to the expression
2790            append: if `True`, add to any existing expressions.
2791                Otherwise, this flattens all the `Group` expression into a single expression.
2792            dialect: the dialect used to parse the input expression.
2793            copy: if `False`, modify this expression instance in-place.
2794            opts: other options to use to parse the input expressions.
2795
2796        Returns:
2797            The modified Select expression.
2798        """
2799        if not expressions:
2800            return self if not copy else self.copy()
2801
2802        return _apply_child_list_builder(
2803            *expressions,
2804            instance=self,
2805            arg="group",
2806            append=append,
2807            copy=copy,
2808            prefix="GROUP BY",
2809            into=Group,
2810            dialect=dialect,
2811            **opts,
2812        )
2813
2814    def order_by(
2815        self,
2816        *expressions: t.Optional[ExpOrStr],
2817        append: bool = True,
2818        dialect: DialectType = None,
2819        copy: bool = True,
2820        **opts,
2821    ) -> Select:
2822        """
2823        Set the ORDER BY expression.
2824
2825        Example:
2826            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2827            'SELECT x FROM tbl ORDER BY x DESC'
2828
2829        Args:
2830            *expressions: the SQL code strings to parse.
2831                If a `Group` instance is passed, this is used as-is.
2832                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2833            append: if `True`, add to any existing expressions.
2834                Otherwise, this flattens all the `Order` expression into a single expression.
2835            dialect: the dialect used to parse the input expression.
2836            copy: if `False`, modify this expression instance in-place.
2837            opts: other options to use to parse the input expressions.
2838
2839        Returns:
2840            The modified Select expression.
2841        """
2842        return _apply_child_list_builder(
2843            *expressions,
2844            instance=self,
2845            arg="order",
2846            append=append,
2847            copy=copy,
2848            prefix="ORDER BY",
2849            into=Order,
2850            dialect=dialect,
2851            **opts,
2852        )
2853
2854    def sort_by(
2855        self,
2856        *expressions: t.Optional[ExpOrStr],
2857        append: bool = True,
2858        dialect: DialectType = None,
2859        copy: bool = True,
2860        **opts,
2861    ) -> Select:
2862        """
2863        Set the SORT BY expression.
2864
2865        Example:
2866            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2867            'SELECT x FROM tbl SORT BY x DESC'
2868
2869        Args:
2870            *expressions: the SQL code strings to parse.
2871                If a `Group` instance is passed, this is used as-is.
2872                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2873            append: if `True`, add to any existing expressions.
2874                Otherwise, this flattens all the `Order` expression into a single expression.
2875            dialect: the dialect used to parse the input expression.
2876            copy: if `False`, modify this expression instance in-place.
2877            opts: other options to use to parse the input expressions.
2878
2879        Returns:
2880            The modified Select expression.
2881        """
2882        return _apply_child_list_builder(
2883            *expressions,
2884            instance=self,
2885            arg="sort",
2886            append=append,
2887            copy=copy,
2888            prefix="SORT BY",
2889            into=Sort,
2890            dialect=dialect,
2891            **opts,
2892        )
2893
2894    def cluster_by(
2895        self,
2896        *expressions: t.Optional[ExpOrStr],
2897        append: bool = True,
2898        dialect: DialectType = None,
2899        copy: bool = True,
2900        **opts,
2901    ) -> Select:
2902        """
2903        Set the CLUSTER BY expression.
2904
2905        Example:
2906            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2907            'SELECT x FROM tbl CLUSTER BY x DESC'
2908
2909        Args:
2910            *expressions: the SQL code strings to parse.
2911                If a `Group` instance is passed, this is used as-is.
2912                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2913            append: if `True`, add to any existing expressions.
2914                Otherwise, this flattens all the `Order` expression into a single expression.
2915            dialect: the dialect used to parse the input expression.
2916            copy: if `False`, modify this expression instance in-place.
2917            opts: other options to use to parse the input expressions.
2918
2919        Returns:
2920            The modified Select expression.
2921        """
2922        return _apply_child_list_builder(
2923            *expressions,
2924            instance=self,
2925            arg="cluster",
2926            append=append,
2927            copy=copy,
2928            prefix="CLUSTER BY",
2929            into=Cluster,
2930            dialect=dialect,
2931            **opts,
2932        )
2933
2934    def limit(
2935        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2936    ) -> Select:
2937        """
2938        Set the LIMIT expression.
2939
2940        Example:
2941            >>> Select().from_("tbl").select("x").limit(10).sql()
2942            'SELECT x FROM tbl LIMIT 10'
2943
2944        Args:
2945            expression: the SQL code string to parse.
2946                This can also be an integer.
2947                If a `Limit` instance is passed, this is used as-is.
2948                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2949            dialect: the dialect used to parse the input expression.
2950            copy: if `False`, modify this expression instance in-place.
2951            opts: other options to use to parse the input expressions.
2952
2953        Returns:
2954            Select: the modified expression.
2955        """
2956        return _apply_builder(
2957            expression=expression,
2958            instance=self,
2959            arg="limit",
2960            into=Limit,
2961            prefix="LIMIT",
2962            dialect=dialect,
2963            copy=copy,
2964            into_arg="expression",
2965            **opts,
2966        )
2967
2968    def offset(
2969        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2970    ) -> Select:
2971        """
2972        Set the OFFSET expression.
2973
2974        Example:
2975            >>> Select().from_("tbl").select("x").offset(10).sql()
2976            'SELECT x FROM tbl OFFSET 10'
2977
2978        Args:
2979            expression: the SQL code string to parse.
2980                This can also be an integer.
2981                If a `Offset` instance is passed, this is used as-is.
2982                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2983            dialect: the dialect used to parse the input expression.
2984            copy: if `False`, modify this expression instance in-place.
2985            opts: other options to use to parse the input expressions.
2986
2987        Returns:
2988            The modified Select expression.
2989        """
2990        return _apply_builder(
2991            expression=expression,
2992            instance=self,
2993            arg="offset",
2994            into=Offset,
2995            prefix="OFFSET",
2996            dialect=dialect,
2997            copy=copy,
2998            into_arg="expression",
2999            **opts,
3000        )
3001
3002    def select(
3003        self,
3004        *expressions: t.Optional[ExpOrStr],
3005        append: bool = True,
3006        dialect: DialectType = None,
3007        copy: bool = True,
3008        **opts,
3009    ) -> Select:
3010        """
3011        Append to or set the SELECT expressions.
3012
3013        Example:
3014            >>> Select().select("x", "y").sql()
3015            'SELECT x, y'
3016
3017        Args:
3018            *expressions: the SQL code strings to parse.
3019                If an `Expression` instance is passed, it will be used as-is.
3020            append: if `True`, add to any existing expressions.
3021                Otherwise, this resets the expressions.
3022            dialect: the dialect used to parse the input expressions.
3023            copy: if `False`, modify this expression instance in-place.
3024            opts: other options to use to parse the input expressions.
3025
3026        Returns:
3027            The modified Select expression.
3028        """
3029        return _apply_list_builder(
3030            *expressions,
3031            instance=self,
3032            arg="expressions",
3033            append=append,
3034            dialect=dialect,
3035            copy=copy,
3036            **opts,
3037        )
3038
3039    def lateral(
3040        self,
3041        *expressions: t.Optional[ExpOrStr],
3042        append: bool = True,
3043        dialect: DialectType = None,
3044        copy: bool = True,
3045        **opts,
3046    ) -> Select:
3047        """
3048        Append to or set the LATERAL expressions.
3049
3050        Example:
3051            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3052            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3053
3054        Args:
3055            *expressions: the SQL code strings to parse.
3056                If an `Expression` instance is passed, it will be used as-is.
3057            append: if `True`, add to any existing expressions.
3058                Otherwise, this resets the expressions.
3059            dialect: the dialect used to parse the input expressions.
3060            copy: if `False`, modify this expression instance in-place.
3061            opts: other options to use to parse the input expressions.
3062
3063        Returns:
3064            The modified Select expression.
3065        """
3066        return _apply_list_builder(
3067            *expressions,
3068            instance=self,
3069            arg="laterals",
3070            append=append,
3071            into=Lateral,
3072            prefix="LATERAL VIEW",
3073            dialect=dialect,
3074            copy=copy,
3075            **opts,
3076        )
3077
3078    def join(
3079        self,
3080        expression: ExpOrStr,
3081        on: t.Optional[ExpOrStr] = None,
3082        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3083        append: bool = True,
3084        join_type: t.Optional[str] = None,
3085        join_alias: t.Optional[Identifier | str] = None,
3086        dialect: DialectType = None,
3087        copy: bool = True,
3088        **opts,
3089    ) -> Select:
3090        """
3091        Append to or set the JOIN expressions.
3092
3093        Example:
3094            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3095            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3096
3097            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3098            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3099
3100            Use `join_type` to change the type of join:
3101
3102            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3103            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3104
3105        Args:
3106            expression: the SQL code string to parse.
3107                If an `Expression` instance is passed, it will be used as-is.
3108            on: optionally specify the join "on" criteria as a SQL string.
3109                If an `Expression` instance is passed, it will be used as-is.
3110            using: optionally specify the join "using" criteria as a SQL string.
3111                If an `Expression` instance is passed, it will be used as-is.
3112            append: if `True`, add to any existing expressions.
3113                Otherwise, this resets the expressions.
3114            join_type: if set, alter the parsed join type.
3115            join_alias: an optional alias for the joined source.
3116            dialect: the dialect used to parse the input expressions.
3117            copy: if `False`, modify this expression instance in-place.
3118            opts: other options to use to parse the input expressions.
3119
3120        Returns:
3121            Select: the modified expression.
3122        """
3123        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3124
3125        try:
3126            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3127        except ParseError:
3128            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3129
3130        join = expression if isinstance(expression, Join) else Join(this=expression)
3131
3132        if isinstance(join.this, Select):
3133            join.this.replace(join.this.subquery())
3134
3135        if join_type:
3136            method: t.Optional[Token]
3137            side: t.Optional[Token]
3138            kind: t.Optional[Token]
3139
3140            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3141
3142            if method:
3143                join.set("method", method.text)
3144            if side:
3145                join.set("side", side.text)
3146            if kind:
3147                join.set("kind", kind.text)
3148
3149        if on:
3150            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3151            join.set("on", on)
3152
3153        if using:
3154            join = _apply_list_builder(
3155                *ensure_list(using),
3156                instance=join,
3157                arg="using",
3158                append=append,
3159                copy=copy,
3160                into=Identifier,
3161                **opts,
3162            )
3163
3164        if join_alias:
3165            join.set("this", alias_(join.this, join_alias, table=True))
3166
3167        return _apply_list_builder(
3168            join,
3169            instance=self,
3170            arg="joins",
3171            append=append,
3172            copy=copy,
3173            **opts,
3174        )
3175
3176    def where(
3177        self,
3178        *expressions: t.Optional[ExpOrStr],
3179        append: bool = True,
3180        dialect: DialectType = None,
3181        copy: bool = True,
3182        **opts,
3183    ) -> Select:
3184        """
3185        Append to or set the WHERE expressions.
3186
3187        Example:
3188            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3189            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3190
3191        Args:
3192            *expressions: the SQL code strings to parse.
3193                If an `Expression` instance is passed, it will be used as-is.
3194                Multiple expressions are combined with an AND operator.
3195            append: if `True`, AND the new expressions to any existing expression.
3196                Otherwise, this resets the expression.
3197            dialect: the dialect used to parse the input expressions.
3198            copy: if `False`, modify this expression instance in-place.
3199            opts: other options to use to parse the input expressions.
3200
3201        Returns:
3202            Select: the modified expression.
3203        """
3204        return _apply_conjunction_builder(
3205            *expressions,
3206            instance=self,
3207            arg="where",
3208            append=append,
3209            into=Where,
3210            dialect=dialect,
3211            copy=copy,
3212            **opts,
3213        )
3214
3215    def having(
3216        self,
3217        *expressions: t.Optional[ExpOrStr],
3218        append: bool = True,
3219        dialect: DialectType = None,
3220        copy: bool = True,
3221        **opts,
3222    ) -> Select:
3223        """
3224        Append to or set the HAVING expressions.
3225
3226        Example:
3227            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3228            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3229
3230        Args:
3231            *expressions: the SQL code strings to parse.
3232                If an `Expression` instance is passed, it will be used as-is.
3233                Multiple expressions are combined with an AND operator.
3234            append: if `True`, AND the new expressions to any existing expression.
3235                Otherwise, this resets the expression.
3236            dialect: the dialect used to parse the input expressions.
3237            copy: if `False`, modify this expression instance in-place.
3238            opts: other options to use to parse the input expressions.
3239
3240        Returns:
3241            The modified Select expression.
3242        """
3243        return _apply_conjunction_builder(
3244            *expressions,
3245            instance=self,
3246            arg="having",
3247            append=append,
3248            into=Having,
3249            dialect=dialect,
3250            copy=copy,
3251            **opts,
3252        )
3253
3254    def window(
3255        self,
3256        *expressions: t.Optional[ExpOrStr],
3257        append: bool = True,
3258        dialect: DialectType = None,
3259        copy: bool = True,
3260        **opts,
3261    ) -> Select:
3262        return _apply_list_builder(
3263            *expressions,
3264            instance=self,
3265            arg="windows",
3266            append=append,
3267            into=Window,
3268            dialect=dialect,
3269            copy=copy,
3270            **opts,
3271        )
3272
3273    def qualify(
3274        self,
3275        *expressions: t.Optional[ExpOrStr],
3276        append: bool = True,
3277        dialect: DialectType = None,
3278        copy: bool = True,
3279        **opts,
3280    ) -> Select:
3281        return _apply_conjunction_builder(
3282            *expressions,
3283            instance=self,
3284            arg="qualify",
3285            append=append,
3286            into=Qualify,
3287            dialect=dialect,
3288            copy=copy,
3289            **opts,
3290        )
3291
3292    def distinct(
3293        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3294    ) -> Select:
3295        """
3296        Set the OFFSET expression.
3297
3298        Example:
3299            >>> Select().from_("tbl").select("x").distinct().sql()
3300            'SELECT DISTINCT x FROM tbl'
3301
3302        Args:
3303            ons: the expressions to distinct on
3304            distinct: whether the Select should be distinct
3305            copy: if `False`, modify this expression instance in-place.
3306
3307        Returns:
3308            Select: the modified expression.
3309        """
3310        instance = maybe_copy(self, copy)
3311        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3312        instance.set("distinct", Distinct(on=on) if distinct else None)
3313        return instance
3314
3315    def ctas(
3316        self,
3317        table: ExpOrStr,
3318        properties: t.Optional[t.Dict] = None,
3319        dialect: DialectType = None,
3320        copy: bool = True,
3321        **opts,
3322    ) -> Create:
3323        """
3324        Convert this expression to a CREATE TABLE AS statement.
3325
3326        Example:
3327            >>> Select().select("*").from_("tbl").ctas("x").sql()
3328            'CREATE TABLE x AS SELECT * FROM tbl'
3329
3330        Args:
3331            table: the SQL code string to parse as the table name.
3332                If another `Expression` instance is passed, it will be used as-is.
3333            properties: an optional mapping of table properties
3334            dialect: the dialect used to parse the input table.
3335            copy: if `False`, modify this expression instance in-place.
3336            opts: other options to use to parse the input table.
3337
3338        Returns:
3339            The new Create expression.
3340        """
3341        instance = maybe_copy(self, copy)
3342        table_expression = maybe_parse(
3343            table,
3344            into=Table,
3345            dialect=dialect,
3346            **opts,
3347        )
3348        properties_expression = None
3349        if properties:
3350            properties_expression = Properties.from_dict(properties)
3351
3352        return Create(
3353            this=table_expression,
3354            kind="table",
3355            expression=instance,
3356            properties=properties_expression,
3357        )
3358
3359    def lock(self, update: bool = True, copy: bool = True) -> Select:
3360        """
3361        Set the locking read mode for this expression.
3362
3363        Examples:
3364            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3365            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3366
3367            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3368            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3369
3370        Args:
3371            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3372            copy: if `False`, modify this expression instance in-place.
3373
3374        Returns:
3375            The modified expression.
3376        """
3377        inst = maybe_copy(self, copy)
3378        inst.set("locks", [Lock(update=update)])
3379
3380        return inst
3381
3382    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3383        """
3384        Set hints for this expression.
3385
3386        Examples:
3387            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3388            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3389
3390        Args:
3391            hints: The SQL code strings to parse as the hints.
3392                If an `Expression` instance is passed, it will be used as-is.
3393            dialect: The dialect used to parse the hints.
3394            copy: If `False`, modify this expression instance in-place.
3395
3396        Returns:
3397            The modified expression.
3398        """
3399        inst = maybe_copy(self, copy)
3400        inst.set(
3401            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3402        )
3403
3404        return inst
3405
3406    @property
3407    def named_selects(self) -> t.List[str]:
3408        return [e.output_name for e in self.expressions if e.alias_or_name]
3409
3410    @property
3411    def is_star(self) -> bool:
3412        return any(expression.is_star for expression in self.expressions)
3413
3414    @property
3415    def selects(self) -> t.List[Expression]:
3416        return self.expressions
3417
3418
3419class Subquery(DerivedTable, Unionable):
3420    arg_types = {
3421        "this": True,
3422        "alias": False,
3423        "with": False,
3424        **QUERY_MODIFIERS,
3425    }
3426
3427    def unnest(self):
3428        """
3429        Returns the first non subquery.
3430        """
3431        expression = self
3432        while isinstance(expression, Subquery):
3433            expression = expression.this
3434        return expression
3435
3436    def unwrap(self) -> Subquery:
3437        expression = self
3438        while expression.same_parent and expression.is_wrapper:
3439            expression = t.cast(Subquery, expression.parent)
3440        return expression
3441
3442    @property
3443    def is_wrapper(self) -> bool:
3444        """
3445        Whether this Subquery acts as a simple wrapper around another expression.
3446
3447        SELECT * FROM (((SELECT * FROM t)))
3448                      ^
3449                      This corresponds to a "wrapper" Subquery node
3450        """
3451        return all(v is None for k, v in self.args.items() if k != "this")
3452
3453    @property
3454    def is_star(self) -> bool:
3455        return self.this.is_star
3456
3457    @property
3458    def output_name(self) -> str:
3459        return self.alias
3460
3461
3462class TableSample(Expression):
3463    arg_types = {
3464        "this": False,
3465        "expressions": False,
3466        "method": False,
3467        "bucket_numerator": False,
3468        "bucket_denominator": False,
3469        "bucket_field": False,
3470        "percent": False,
3471        "rows": False,
3472        "size": False,
3473        "seed": False,
3474        "kind": False,
3475    }
3476
3477
3478class Tag(Expression):
3479    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3480
3481    arg_types = {
3482        "this": False,
3483        "prefix": False,
3484        "postfix": False,
3485    }
3486
3487
3488# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3489# https://duckdb.org/docs/sql/statements/pivot
3490class Pivot(Expression):
3491    arg_types = {
3492        "this": False,
3493        "alias": False,
3494        "expressions": False,
3495        "field": False,
3496        "unpivot": False,
3497        "using": False,
3498        "group": False,
3499        "columns": False,
3500        "include_nulls": False,
3501    }
3502
3503
3504class Window(Condition):
3505    arg_types = {
3506        "this": True,
3507        "partition_by": False,
3508        "order": False,
3509        "spec": False,
3510        "alias": False,
3511        "over": False,
3512        "first": False,
3513    }
3514
3515
3516class WindowSpec(Expression):
3517    arg_types = {
3518        "kind": False,
3519        "start": False,
3520        "start_side": False,
3521        "end": False,
3522        "end_side": False,
3523    }
3524
3525
3526class Where(Expression):
3527    pass
3528
3529
3530class Star(Expression):
3531    arg_types = {"except": False, "replace": False}
3532
3533    @property
3534    def name(self) -> str:
3535        return "*"
3536
3537    @property
3538    def output_name(self) -> str:
3539        return self.name
3540
3541
3542class Parameter(Condition):
3543    arg_types = {"this": True, "expression": False}
3544
3545
3546class SessionParameter(Condition):
3547    arg_types = {"this": True, "kind": False}
3548
3549
3550class Placeholder(Condition):
3551    arg_types = {"this": False, "kind": False}
3552
3553
3554class Null(Condition):
3555    arg_types: t.Dict[str, t.Any] = {}
3556
3557    @property
3558    def name(self) -> str:
3559        return "NULL"
3560
3561
3562class Boolean(Condition):
3563    pass
3564
3565
3566class DataTypeParam(Expression):
3567    arg_types = {"this": True, "expression": False}
3568
3569
3570class DataType(Expression):
3571    arg_types = {
3572        "this": True,
3573        "expressions": False,
3574        "nested": False,
3575        "values": False,
3576        "prefix": False,
3577        "kind": False,
3578    }
3579
3580    class Type(AutoName):
3581        ARRAY = auto()
3582        BIGDECIMAL = auto()
3583        BIGINT = auto()
3584        BIGSERIAL = auto()
3585        BINARY = auto()
3586        BIT = auto()
3587        BOOLEAN = auto()
3588        CHAR = auto()
3589        DATE = auto()
3590        DATEMULTIRANGE = auto()
3591        DATERANGE = auto()
3592        DATETIME = auto()
3593        DATETIME64 = auto()
3594        DECIMAL = auto()
3595        DOUBLE = auto()
3596        ENUM = auto()
3597        ENUM8 = auto()
3598        ENUM16 = auto()
3599        FIXEDSTRING = auto()
3600        FLOAT = auto()
3601        GEOGRAPHY = auto()
3602        GEOMETRY = auto()
3603        HLLSKETCH = auto()
3604        HSTORE = auto()
3605        IMAGE = auto()
3606        INET = auto()
3607        INT = auto()
3608        INT128 = auto()
3609        INT256 = auto()
3610        INT4MULTIRANGE = auto()
3611        INT4RANGE = auto()
3612        INT8MULTIRANGE = auto()
3613        INT8RANGE = auto()
3614        INTERVAL = auto()
3615        IPADDRESS = auto()
3616        IPPREFIX = auto()
3617        JSON = auto()
3618        JSONB = auto()
3619        LONGBLOB = auto()
3620        LONGTEXT = auto()
3621        LOWCARDINALITY = auto()
3622        MAP = auto()
3623        MEDIUMBLOB = auto()
3624        MEDIUMINT = auto()
3625        MEDIUMTEXT = auto()
3626        MONEY = auto()
3627        NCHAR = auto()
3628        NESTED = auto()
3629        NULL = auto()
3630        NULLABLE = auto()
3631        NUMMULTIRANGE = auto()
3632        NUMRANGE = auto()
3633        NVARCHAR = auto()
3634        OBJECT = auto()
3635        ROWVERSION = auto()
3636        SERIAL = auto()
3637        SET = auto()
3638        SMALLINT = auto()
3639        SMALLMONEY = auto()
3640        SMALLSERIAL = auto()
3641        STRUCT = auto()
3642        SUPER = auto()
3643        TEXT = auto()
3644        TINYBLOB = auto()
3645        TINYTEXT = auto()
3646        TIME = auto()
3647        TIMETZ = auto()
3648        TIMESTAMP = auto()
3649        TIMESTAMPLTZ = auto()
3650        TIMESTAMPTZ = auto()
3651        TIMESTAMP_S = auto()
3652        TIMESTAMP_MS = auto()
3653        TIMESTAMP_NS = auto()
3654        TINYINT = auto()
3655        TSMULTIRANGE = auto()
3656        TSRANGE = auto()
3657        TSTZMULTIRANGE = auto()
3658        TSTZRANGE = auto()
3659        UBIGINT = auto()
3660        UINT = auto()
3661        UINT128 = auto()
3662        UINT256 = auto()
3663        UMEDIUMINT = auto()
3664        UDECIMAL = auto()
3665        UNIQUEIDENTIFIER = auto()
3666        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3667        USERDEFINED = "USER-DEFINED"
3668        USMALLINT = auto()
3669        UTINYINT = auto()
3670        UUID = auto()
3671        VARBINARY = auto()
3672        VARCHAR = auto()
3673        VARIANT = auto()
3674        XML = auto()
3675        YEAR = auto()
3676
3677    TEXT_TYPES = {
3678        Type.CHAR,
3679        Type.NCHAR,
3680        Type.VARCHAR,
3681        Type.NVARCHAR,
3682        Type.TEXT,
3683    }
3684
3685    INTEGER_TYPES = {
3686        Type.INT,
3687        Type.TINYINT,
3688        Type.SMALLINT,
3689        Type.BIGINT,
3690        Type.INT128,
3691        Type.INT256,
3692    }
3693
3694    FLOAT_TYPES = {
3695        Type.FLOAT,
3696        Type.DOUBLE,
3697    }
3698
3699    NUMERIC_TYPES = {
3700        *INTEGER_TYPES,
3701        *FLOAT_TYPES,
3702    }
3703
3704    TEMPORAL_TYPES = {
3705        Type.TIME,
3706        Type.TIMETZ,
3707        Type.TIMESTAMP,
3708        Type.TIMESTAMPTZ,
3709        Type.TIMESTAMPLTZ,
3710        Type.TIMESTAMP_S,
3711        Type.TIMESTAMP_MS,
3712        Type.TIMESTAMP_NS,
3713        Type.DATE,
3714        Type.DATETIME,
3715        Type.DATETIME64,
3716    }
3717
3718    @classmethod
3719    def build(
3720        cls,
3721        dtype: str | DataType | DataType.Type,
3722        dialect: DialectType = None,
3723        udt: bool = False,
3724        **kwargs,
3725    ) -> DataType:
3726        """
3727        Constructs a DataType object.
3728
3729        Args:
3730            dtype: the data type of interest.
3731            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3732            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3733                DataType, thus creating a user-defined type.
3734            kawrgs: additional arguments to pass in the constructor of DataType.
3735
3736        Returns:
3737            The constructed DataType object.
3738        """
3739        from sqlglot import parse_one
3740
3741        if isinstance(dtype, str):
3742            if dtype.upper() == "UNKNOWN":
3743                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3744
3745            try:
3746                data_type_exp = parse_one(
3747                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3748                )
3749            except ParseError:
3750                if udt:
3751                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3752                raise
3753        elif isinstance(dtype, DataType.Type):
3754            data_type_exp = DataType(this=dtype)
3755        elif isinstance(dtype, DataType):
3756            return dtype
3757        else:
3758            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3759
3760        return DataType(**{**data_type_exp.args, **kwargs})
3761
3762    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3763        """
3764        Checks whether this DataType matches one of the provided data types. Nested types or precision
3765        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3766
3767        Args:
3768            dtypes: the data types to compare this DataType to.
3769
3770        Returns:
3771            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3772        """
3773        for dtype in dtypes:
3774            other = DataType.build(dtype, udt=True)
3775
3776            if (
3777                other.expressions
3778                or self.this == DataType.Type.USERDEFINED
3779                or other.this == DataType.Type.USERDEFINED
3780            ):
3781                matches = self == other
3782            else:
3783                matches = self.this == other.this
3784
3785            if matches:
3786                return True
3787        return False
3788
3789
3790# https://www.postgresql.org/docs/15/datatype-pseudo.html
3791class PseudoType(DataType):
3792    arg_types = {"this": True}
3793
3794
3795# https://www.postgresql.org/docs/15/datatype-oid.html
3796class ObjectIdentifier(DataType):
3797    arg_types = {"this": True}
3798
3799
3800# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3801class SubqueryPredicate(Predicate):
3802    pass
3803
3804
3805class All(SubqueryPredicate):
3806    pass
3807
3808
3809class Any(SubqueryPredicate):
3810    pass
3811
3812
3813class Exists(SubqueryPredicate):
3814    pass
3815
3816
3817# Commands to interact with the databases or engines. For most of the command
3818# expressions we parse whatever comes after the command's name as a string.
3819class Command(Expression):
3820    arg_types = {"this": True, "expression": False}
3821
3822
3823class Transaction(Expression):
3824    arg_types = {"this": False, "modes": False, "mark": False}
3825
3826
3827class Commit(Expression):
3828    arg_types = {"chain": False, "this": False, "durability": False}
3829
3830
3831class Rollback(Expression):
3832    arg_types = {"savepoint": False, "this": False}
3833
3834
3835class AlterTable(Expression):
3836    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3837
3838
3839class AddConstraint(Expression):
3840    arg_types = {"this": False, "expression": False, "enforced": False}
3841
3842
3843class DropPartition(Expression):
3844    arg_types = {"expressions": True, "exists": False}
3845
3846
3847# Binary expressions like (ADD a b)
3848class Binary(Condition):
3849    arg_types = {"this": True, "expression": True}
3850
3851    @property
3852    def left(self) -> Expression:
3853        return self.this
3854
3855    @property
3856    def right(self) -> Expression:
3857        return self.expression
3858
3859
3860class Add(Binary):
3861    pass
3862
3863
3864class Connector(Binary):
3865    pass
3866
3867
3868class And(Connector):
3869    pass
3870
3871
3872class Or(Connector):
3873    pass
3874
3875
3876class BitwiseAnd(Binary):
3877    pass
3878
3879
3880class BitwiseLeftShift(Binary):
3881    pass
3882
3883
3884class BitwiseOr(Binary):
3885    pass
3886
3887
3888class BitwiseRightShift(Binary):
3889    pass
3890
3891
3892class BitwiseXor(Binary):
3893    pass
3894
3895
3896class Div(Binary):
3897    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3898
3899
3900class Overlaps(Binary):
3901    pass
3902
3903
3904class Dot(Binary):
3905    @property
3906    def name(self) -> str:
3907        return self.expression.name
3908
3909    @property
3910    def output_name(self) -> str:
3911        return self.name
3912
3913    @classmethod
3914    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3915        """Build a Dot object with a sequence of expressions."""
3916        if len(expressions) < 2:
3917            raise ValueError(f"Dot requires >= 2 expressions.")
3918
3919        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3920
3921
3922class DPipe(Binary):
3923    pass
3924
3925
3926class SafeDPipe(DPipe):
3927    pass
3928
3929
3930class EQ(Binary, Predicate):
3931    pass
3932
3933
3934class NullSafeEQ(Binary, Predicate):
3935    pass
3936
3937
3938class NullSafeNEQ(Binary, Predicate):
3939    pass
3940
3941
3942# Represents e.g. := in DuckDB which is mostly used for setting parameters
3943class PropertyEQ(Binary):
3944    pass
3945
3946
3947class Distance(Binary):
3948    pass
3949
3950
3951class Escape(Binary):
3952    pass
3953
3954
3955class Glob(Binary, Predicate):
3956    pass
3957
3958
3959class GT(Binary, Predicate):
3960    pass
3961
3962
3963class GTE(Binary, Predicate):
3964    pass
3965
3966
3967class ILike(Binary, Predicate):
3968    pass
3969
3970
3971class ILikeAny(Binary, Predicate):
3972    pass
3973
3974
3975class IntDiv(Binary):
3976    pass
3977
3978
3979class Is(Binary, Predicate):
3980    pass
3981
3982
3983class Kwarg(Binary):
3984    """Kwarg in special functions like func(kwarg => y)."""
3985
3986
3987class Like(Binary, Predicate):
3988    pass
3989
3990
3991class LikeAny(Binary, Predicate):
3992    pass
3993
3994
3995class LT(Binary, Predicate):
3996    pass
3997
3998
3999class LTE(Binary, Predicate):
4000    pass
4001
4002
4003class Mod(Binary):
4004    pass
4005
4006
4007class Mul(Binary):
4008    pass
4009
4010
4011class NEQ(Binary, Predicate):
4012    pass
4013
4014
4015class SimilarTo(Binary, Predicate):
4016    pass
4017
4018
4019class Slice(Binary):
4020    arg_types = {"this": False, "expression": False}
4021
4022
4023class Sub(Binary):
4024    pass
4025
4026
4027class ArrayOverlaps(Binary):
4028    pass
4029
4030
4031# Unary Expressions
4032# (NOT a)
4033class Unary(Condition):
4034    pass
4035
4036
4037class BitwiseNot(Unary):
4038    pass
4039
4040
4041class Not(Unary):
4042    pass
4043
4044
4045class Paren(Unary):
4046    arg_types = {"this": True, "with": False}
4047
4048    @property
4049    def output_name(self) -> str:
4050        return self.this.name
4051
4052
4053class Neg(Unary):
4054    pass
4055
4056
4057class Alias(Expression):
4058    arg_types = {"this": True, "alias": False}
4059
4060    @property
4061    def output_name(self) -> str:
4062        return self.alias
4063
4064
4065class Aliases(Expression):
4066    arg_types = {"this": True, "expressions": True}
4067
4068    @property
4069    def aliases(self):
4070        return self.expressions
4071
4072
4073class AtTimeZone(Expression):
4074    arg_types = {"this": True, "zone": True}
4075
4076
4077class Between(Predicate):
4078    arg_types = {"this": True, "low": True, "high": True}
4079
4080
4081class Bracket(Condition):
4082    arg_types = {"this": True, "expressions": True}
4083
4084    @property
4085    def output_name(self) -> str:
4086        if len(self.expressions) == 1:
4087            return self.expressions[0].output_name
4088
4089        return super().output_name
4090
4091
4092class SafeBracket(Bracket):
4093    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4094
4095
4096class Distinct(Expression):
4097    arg_types = {"expressions": False, "on": False}
4098
4099
4100class In(Predicate):
4101    arg_types = {
4102        "this": True,
4103        "expressions": False,
4104        "query": False,
4105        "unnest": False,
4106        "field": False,
4107        "is_global": False,
4108    }
4109
4110
4111# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4112class ForIn(Expression):
4113    arg_types = {"this": True, "expression": True}
4114
4115
4116class TimeUnit(Expression):
4117    """Automatically converts unit arg into a var."""
4118
4119    arg_types = {"unit": False}
4120
4121    UNABBREVIATED_UNIT_NAME = {
4122        "d": "day",
4123        "h": "hour",
4124        "m": "minute",
4125        "ms": "millisecond",
4126        "ns": "nanosecond",
4127        "q": "quarter",
4128        "s": "second",
4129        "us": "microsecond",
4130        "w": "week",
4131        "y": "year",
4132    }
4133
4134    VAR_LIKE = (Column, Literal, Var)
4135
4136    def __init__(self, **args):
4137        unit = args.get("unit")
4138        if isinstance(unit, self.VAR_LIKE):
4139            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4140        elif isinstance(unit, Week):
4141            unit.set("this", Var(this=unit.this.name))
4142
4143        super().__init__(**args)
4144
4145    @property
4146    def unit(self) -> t.Optional[Var]:
4147        return self.args.get("unit")
4148
4149
4150class IntervalOp(TimeUnit):
4151    arg_types = {"unit": True, "expression": True}
4152
4153    def interval(self):
4154        return Interval(
4155            this=self.expression.copy(),
4156            unit=self.unit.copy(),
4157        )
4158
4159
4160# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4161# https://trino.io/docs/current/language/types.html#interval-day-to-second
4162# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4163class IntervalSpan(DataType):
4164    arg_types = {"this": True, "expression": True}
4165
4166
4167class Interval(TimeUnit):
4168    arg_types = {"this": False, "unit": False}
4169
4170
4171class IgnoreNulls(Expression):
4172    pass
4173
4174
4175class RespectNulls(Expression):
4176    pass
4177
4178
4179# Functions
4180class Func(Condition):
4181    """
4182    The base class for all function expressions.
4183
4184    Attributes:
4185        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4186            treated as a variable length argument and the argument's value will be stored as a list.
4187        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4188            for this function expression. These values are used to map this node to a name during parsing
4189            as well as to provide the function's name during SQL string generation. By default the SQL
4190            name is set to the expression's class name transformed to snake case.
4191    """
4192
4193    is_var_len_args = False
4194
4195    @classmethod
4196    def from_arg_list(cls, args):
4197        if cls.is_var_len_args:
4198            all_arg_keys = list(cls.arg_types)
4199            # If this function supports variable length argument treat the last argument as such.
4200            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4201            num_non_var = len(non_var_len_arg_keys)
4202
4203            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4204            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4205        else:
4206            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4207
4208        return cls(**args_dict)
4209
4210    @classmethod
4211    def sql_names(cls):
4212        if cls is Func:
4213            raise NotImplementedError(
4214                "SQL name is only supported by concrete function implementations"
4215            )
4216        if "_sql_names" not in cls.__dict__:
4217            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4218        return cls._sql_names
4219
4220    @classmethod
4221    def sql_name(cls):
4222        return cls.sql_names()[0]
4223
4224    @classmethod
4225    def default_parser_mappings(cls):
4226        return {name: cls.from_arg_list for name in cls.sql_names()}
4227
4228
4229class AggFunc(Func):
4230    pass
4231
4232
4233class ParameterizedAgg(AggFunc):
4234    arg_types = {"this": True, "expressions": True, "params": True}
4235
4236
4237class Abs(Func):
4238    pass
4239
4240
4241class ArgMax(AggFunc):
4242    arg_types = {"this": True, "expression": True, "count": False}
4243    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4244
4245
4246class ArgMin(AggFunc):
4247    arg_types = {"this": True, "expression": True, "count": False}
4248    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4249
4250
4251class ApproxTopK(AggFunc):
4252    arg_types = {"this": True, "expression": False, "counters": False}
4253
4254
4255class Flatten(Func):
4256    pass
4257
4258
4259# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4260class Transform(Func):
4261    arg_types = {"this": True, "expression": True}
4262
4263
4264class Anonymous(Func):
4265    arg_types = {"this": True, "expressions": False}
4266    is_var_len_args = True
4267
4268
4269# https://docs.snowflake.com/en/sql-reference/functions/hll
4270# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4271class Hll(AggFunc):
4272    arg_types = {"this": True, "expressions": False}
4273    is_var_len_args = True
4274
4275
4276class ApproxDistinct(AggFunc):
4277    arg_types = {"this": True, "accuracy": False}
4278    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4279
4280
4281class Array(Func):
4282    arg_types = {"expressions": False}
4283    is_var_len_args = True
4284
4285
4286# https://docs.snowflake.com/en/sql-reference/functions/to_char
4287class ToChar(Func):
4288    arg_types = {"this": True, "format": False}
4289
4290
4291class GenerateSeries(Func):
4292    arg_types = {"start": True, "end": True, "step": False}
4293
4294
4295class ArrayAgg(AggFunc):
4296    pass
4297
4298
4299class ArrayAll(Func):
4300    arg_types = {"this": True, "expression": True}
4301
4302
4303class ArrayAny(Func):
4304    arg_types = {"this": True, "expression": True}
4305
4306
4307class ArrayConcat(Func):
4308    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4309    arg_types = {"this": True, "expressions": False}
4310    is_var_len_args = True
4311
4312
4313class ArrayContains(Binary, Func):
4314    pass
4315
4316
4317class ArrayContained(Binary):
4318    pass
4319
4320
4321class ArrayFilter(Func):
4322    arg_types = {"this": True, "expression": True}
4323    _sql_names = ["FILTER", "ARRAY_FILTER"]
4324
4325
4326class ArrayJoin(Func):
4327    arg_types = {"this": True, "expression": True, "null": False}
4328
4329
4330class ArraySize(Func):
4331    arg_types = {"this": True, "expression": False}
4332
4333
4334class ArraySort(Func):
4335    arg_types = {"this": True, "expression": False}
4336
4337
4338class ArraySum(Func):
4339    pass
4340
4341
4342class ArrayUnionAgg(AggFunc):
4343    pass
4344
4345
4346class Avg(AggFunc):
4347    pass
4348
4349
4350class AnyValue(AggFunc):
4351    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4352
4353
4354class First(Func):
4355    arg_types = {"this": True, "ignore_nulls": False}
4356
4357
4358class Last(Func):
4359    arg_types = {"this": True, "ignore_nulls": False}
4360
4361
4362class Case(Func):
4363    arg_types = {"this": False, "ifs": True, "default": False}
4364
4365    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4366        instance = maybe_copy(self, copy)
4367        instance.append(
4368            "ifs",
4369            If(
4370                this=maybe_parse(condition, copy=copy, **opts),
4371                true=maybe_parse(then, copy=copy, **opts),
4372            ),
4373        )
4374        return instance
4375
4376    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4377        instance = maybe_copy(self, copy)
4378        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4379        return instance
4380
4381
4382class Cast(Func):
4383    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4384
4385    @property
4386    def name(self) -> str:
4387        return self.this.name
4388
4389    @property
4390    def to(self) -> DataType:
4391        return self.args["to"]
4392
4393    @property
4394    def output_name(self) -> str:
4395        return self.name
4396
4397    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4398        """
4399        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4400        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4401        array<int> != array<float>.
4402
4403        Args:
4404            dtypes: the data types to compare this Cast's DataType to.
4405
4406        Returns:
4407            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4408        """
4409        return self.to.is_type(*dtypes)
4410
4411
4412class TryCast(Cast):
4413    pass
4414
4415
4416class CastToStrType(Func):
4417    arg_types = {"this": True, "to": True}
4418
4419
4420class Collate(Binary, Func):
4421    pass
4422
4423
4424class Ceil(Func):
4425    arg_types = {"this": True, "decimals": False}
4426    _sql_names = ["CEIL", "CEILING"]
4427
4428
4429class Coalesce(Func):
4430    arg_types = {"this": True, "expressions": False}
4431    is_var_len_args = True
4432    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4433
4434
4435class Chr(Func):
4436    arg_types = {"this": True, "charset": False, "expressions": False}
4437    is_var_len_args = True
4438    _sql_names = ["CHR", "CHAR"]
4439
4440
4441class Concat(Func):
4442    arg_types = {"expressions": True}
4443    is_var_len_args = True
4444
4445
4446class SafeConcat(Concat):
4447    pass
4448
4449
4450class ConcatWs(Concat):
4451    _sql_names = ["CONCAT_WS"]
4452
4453
4454class Count(AggFunc):
4455    arg_types = {"this": False, "expressions": False}
4456    is_var_len_args = True
4457
4458
4459class CountIf(AggFunc):
4460    pass
4461
4462
4463class CurrentDate(Func):
4464    arg_types = {"this": False}
4465
4466
4467class CurrentDatetime(Func):
4468    arg_types = {"this": False}
4469
4470
4471class CurrentTime(Func):
4472    arg_types = {"this": False}
4473
4474
4475class CurrentTimestamp(Func):
4476    arg_types = {"this": False}
4477
4478
4479class CurrentUser(Func):
4480    arg_types = {"this": False}
4481
4482
4483class DateAdd(Func, IntervalOp):
4484    arg_types = {"this": True, "expression": True, "unit": False}
4485
4486
4487class DateSub(Func, IntervalOp):
4488    arg_types = {"this": True, "expression": True, "unit": False}
4489
4490
4491class DateDiff(Func, TimeUnit):
4492    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4493    arg_types = {"this": True, "expression": True, "unit": False}
4494
4495
4496class DateTrunc(Func):
4497    arg_types = {"unit": True, "this": True, "zone": False}
4498
4499    @property
4500    def unit(self) -> Expression:
4501        return self.args["unit"]
4502
4503
4504class DatetimeAdd(Func, IntervalOp):
4505    arg_types = {"this": True, "expression": True, "unit": False}
4506
4507
4508class DatetimeSub(Func, IntervalOp):
4509    arg_types = {"this": True, "expression": True, "unit": False}
4510
4511
4512class DatetimeDiff(Func, TimeUnit):
4513    arg_types = {"this": True, "expression": True, "unit": False}
4514
4515
4516class DatetimeTrunc(Func, TimeUnit):
4517    arg_types = {"this": True, "unit": True, "zone": False}
4518
4519
4520class DayOfWeek(Func):
4521    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4522
4523
4524class DayOfMonth(Func):
4525    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4526
4527
4528class DayOfYear(Func):
4529    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4530
4531
4532class ToDays(Func):
4533    pass
4534
4535
4536class WeekOfYear(Func):
4537    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4538
4539
4540class MonthsBetween(Func):
4541    arg_types = {"this": True, "expression": True, "roundoff": False}
4542
4543
4544class LastDateOfMonth(Func):
4545    pass
4546
4547
4548class Extract(Func):
4549    arg_types = {"this": True, "expression": True}
4550
4551
4552class Timestamp(Func):
4553    arg_types = {"this": False, "expression": False}
4554
4555
4556class TimestampAdd(Func, TimeUnit):
4557    arg_types = {"this": True, "expression": True, "unit": False}
4558
4559
4560class TimestampSub(Func, TimeUnit):
4561    arg_types = {"this": True, "expression": True, "unit": False}
4562
4563
4564class TimestampDiff(Func, TimeUnit):
4565    arg_types = {"this": True, "expression": True, "unit": False}
4566
4567
4568class TimestampTrunc(Func, TimeUnit):
4569    arg_types = {"this": True, "unit": True, "zone": False}
4570
4571
4572class TimeAdd(Func, TimeUnit):
4573    arg_types = {"this": True, "expression": True, "unit": False}
4574
4575
4576class TimeSub(Func, TimeUnit):
4577    arg_types = {"this": True, "expression": True, "unit": False}
4578
4579
4580class TimeDiff(Func, TimeUnit):
4581    arg_types = {"this": True, "expression": True, "unit": False}
4582
4583
4584class TimeTrunc(Func, TimeUnit):
4585    arg_types = {"this": True, "unit": True, "zone": False}
4586
4587
4588class DateFromParts(Func):
4589    _sql_names = ["DATEFROMPARTS"]
4590    arg_types = {"year": True, "month": True, "day": True}
4591
4592
4593class DateStrToDate(Func):
4594    pass
4595
4596
4597class DateToDateStr(Func):
4598    pass
4599
4600
4601class DateToDi(Func):
4602    pass
4603
4604
4605# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4606class Date(Func):
4607    arg_types = {"this": False, "zone": False, "expressions": False}
4608    is_var_len_args = True
4609
4610
4611class Day(Func):
4612    pass
4613
4614
4615class Decode(Func):
4616    arg_types = {"this": True, "charset": True, "replace": False}
4617
4618
4619class DiToDate(Func):
4620    pass
4621
4622
4623class Encode(Func):
4624    arg_types = {"this": True, "charset": True}
4625
4626
4627class Exp(Func):
4628    pass
4629
4630
4631# https://docs.snowflake.com/en/sql-reference/functions/flatten
4632class Explode(Func):
4633    arg_types = {"this": True, "expressions": False}
4634    is_var_len_args = True
4635
4636
4637class ExplodeOuter(Explode):
4638    pass
4639
4640
4641class Posexplode(Explode):
4642    pass
4643
4644
4645class PosexplodeOuter(Posexplode):
4646    pass
4647
4648
4649class Floor(Func):
4650    arg_types = {"this": True, "decimals": False}
4651
4652
4653class FromBase64(Func):
4654    pass
4655
4656
4657class ToBase64(Func):
4658    pass
4659
4660
4661class Greatest(Func):
4662    arg_types = {"this": True, "expressions": False}
4663    is_var_len_args = True
4664
4665
4666class GroupConcat(AggFunc):
4667    arg_types = {"this": True, "separator": False}
4668
4669
4670class Hex(Func):
4671    pass
4672
4673
4674class Xor(Connector, Func):
4675    arg_types = {"this": False, "expression": False, "expressions": False}
4676
4677
4678class If(Func):
4679    arg_types = {"this": True, "true": True, "false": False}
4680
4681
4682class Nullif(Func):
4683    arg_types = {"this": True, "expression": True}
4684
4685
4686class Initcap(Func):
4687    arg_types = {"this": True, "expression": False}
4688
4689
4690class IsNan(Func):
4691    _sql_names = ["IS_NAN", "ISNAN"]
4692
4693
4694class FormatJson(Expression):
4695    pass
4696
4697
4698class JSONKeyValue(Expression):
4699    arg_types = {"this": True, "expression": True}
4700
4701
4702class JSONObject(Func):
4703    arg_types = {
4704        "expressions": False,
4705        "null_handling": False,
4706        "unique_keys": False,
4707        "return_type": False,
4708        "encoding": False,
4709    }
4710
4711
4712# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4713class JSONArray(Func):
4714    arg_types = {
4715        "expressions": True,
4716        "null_handling": False,
4717        "return_type": False,
4718        "strict": False,
4719    }
4720
4721
4722# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4723class JSONArrayAgg(Func):
4724    arg_types = {
4725        "this": True,
4726        "order": False,
4727        "null_handling": False,
4728        "return_type": False,
4729        "strict": False,
4730    }
4731
4732
4733# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4734# Note: parsing of JSON column definitions is currently incomplete.
4735class JSONColumnDef(Expression):
4736    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4737
4738
4739class JSONSchema(Expression):
4740    arg_types = {"expressions": True}
4741
4742
4743# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4744class JSONTable(Func):
4745    arg_types = {
4746        "this": True,
4747        "schema": True,
4748        "path": False,
4749        "error_handling": False,
4750        "empty_handling": False,
4751    }
4752
4753
4754class OpenJSONColumnDef(Expression):
4755    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4756
4757
4758class OpenJSON(Func):
4759    arg_types = {"this": True, "path": False, "expressions": False}
4760
4761
4762class JSONBContains(Binary):
4763    _sql_names = ["JSONB_CONTAINS"]
4764
4765
4766class JSONExtract(Binary, Func):
4767    _sql_names = ["JSON_EXTRACT"]
4768
4769
4770class JSONExtractScalar(JSONExtract):
4771    _sql_names = ["JSON_EXTRACT_SCALAR"]
4772
4773
4774class JSONBExtract(JSONExtract):
4775    _sql_names = ["JSONB_EXTRACT"]
4776
4777
4778class JSONBExtractScalar(JSONExtract):
4779    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4780
4781
4782class JSONFormat(Func):
4783    arg_types = {"this": False, "options": False}
4784    _sql_names = ["JSON_FORMAT"]
4785
4786
4787# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4788class JSONArrayContains(Binary, Predicate, Func):
4789    _sql_names = ["JSON_ARRAY_CONTAINS"]
4790
4791
4792class ParseJSON(Func):
4793    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4794    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4795    arg_types = {"this": True, "expressions": False}
4796    is_var_len_args = True
4797
4798
4799class Least(Func):
4800    arg_types = {"this": True, "expressions": False}
4801    is_var_len_args = True
4802
4803
4804class Left(Func):
4805    arg_types = {"this": True, "expression": True}
4806
4807
4808class Right(Func):
4809    arg_types = {"this": True, "expression": True}
4810
4811
4812class Length(Func):
4813    _sql_names = ["LENGTH", "LEN"]
4814
4815
4816class Levenshtein(Func):
4817    arg_types = {
4818        "this": True,
4819        "expression": False,
4820        "ins_cost": False,
4821        "del_cost": False,
4822        "sub_cost": False,
4823    }
4824
4825
4826class Ln(Func):
4827    pass
4828
4829
4830class Log(Func):
4831    arg_types = {"this": True, "expression": False}
4832
4833
4834class Log2(Func):
4835    pass
4836
4837
4838class Log10(Func):
4839    pass
4840
4841
4842class LogicalOr(AggFunc):
4843    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4844
4845
4846class LogicalAnd(AggFunc):
4847    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4848
4849
4850class Lower(Func):
4851    _sql_names = ["LOWER", "LCASE"]
4852
4853
4854class Map(Func):
4855    arg_types = {"keys": False, "values": False}
4856
4857    @property
4858    def keys(self) -> t.List[Expression]:
4859        keys = self.args.get("keys")
4860        return keys.expressions if keys else []
4861
4862    @property
4863    def values(self) -> t.List[Expression]:
4864        values = self.args.get("values")
4865        return values.expressions if values else []
4866
4867
4868class MapFromEntries(Func):
4869    pass
4870
4871
4872class StarMap(Func):
4873    pass
4874
4875
4876class VarMap(Func):
4877    arg_types = {"keys": True, "values": True}
4878    is_var_len_args = True
4879
4880    @property
4881    def keys(self) -> t.List[Expression]:
4882        return self.args["keys"].expressions
4883
4884    @property
4885    def values(self) -> t.List[Expression]:
4886        return self.args["values"].expressions
4887
4888
4889# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4890class MatchAgainst(Func):
4891    arg_types = {"this": True, "expressions": True, "modifier": False}
4892
4893
4894class Max(AggFunc):
4895    arg_types = {"this": True, "expressions": False}
4896    is_var_len_args = True
4897
4898
4899class MD5(Func):
4900    _sql_names = ["MD5"]
4901
4902
4903# Represents the variant of the MD5 function that returns a binary value
4904class MD5Digest(Func):
4905    _sql_names = ["MD5_DIGEST"]
4906
4907
4908class Min(AggFunc):
4909    arg_types = {"this": True, "expressions": False}
4910    is_var_len_args = True
4911
4912
4913class Month(Func):
4914    pass
4915
4916
4917class Nvl2(Func):
4918    arg_types = {"this": True, "true": True, "false": False}
4919
4920
4921# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4922class Predict(Func):
4923    arg_types = {"this": True, "expression": True, "params_struct": False}
4924
4925
4926class Pow(Binary, Func):
4927    _sql_names = ["POWER", "POW"]
4928
4929
4930class PercentileCont(AggFunc):
4931    arg_types = {"this": True, "expression": False}
4932
4933
4934class PercentileDisc(AggFunc):
4935    arg_types = {"this": True, "expression": False}
4936
4937
4938class Quantile(AggFunc):
4939    arg_types = {"this": True, "quantile": True}
4940
4941
4942class ApproxQuantile(Quantile):
4943    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4944
4945
4946class RangeN(Func):
4947    arg_types = {"this": True, "expressions": True, "each": False}
4948
4949
4950class ReadCSV(Func):
4951    _sql_names = ["READ_CSV"]
4952    is_var_len_args = True
4953    arg_types = {"this": True, "expressions": False}
4954
4955
4956class Reduce(Func):
4957    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4958
4959
4960class RegexpExtract(Func):
4961    arg_types = {
4962        "this": True,
4963        "expression": True,
4964        "position": False,
4965        "occurrence": False,
4966        "parameters": False,
4967        "group": False,
4968    }
4969
4970
4971class RegexpReplace(Func):
4972    arg_types = {
4973        "this": True,
4974        "expression": True,
4975        "replacement": True,
4976        "position": False,
4977        "occurrence": False,
4978        "parameters": False,
4979        "modifiers": False,
4980    }
4981
4982
4983class RegexpLike(Binary, Func):
4984    arg_types = {"this": True, "expression": True, "flag": False}
4985
4986
4987class RegexpILike(Binary, Func):
4988    arg_types = {"this": True, "expression": True, "flag": False}
4989
4990
4991# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
4992# limit is the number of times a pattern is applied
4993class RegexpSplit(Func):
4994    arg_types = {"this": True, "expression": True, "limit": False}
4995
4996
4997class Repeat(Func):
4998    arg_types = {"this": True, "times": True}
4999
5000
5001class Round(Func):
5002    arg_types = {"this": True, "decimals": False}
5003
5004
5005class RowNumber(Func):
5006    arg_types: t.Dict[str, t.Any] = {}
5007
5008
5009class SafeDivide(Func):
5010    arg_types = {"this": True, "expression": True}
5011
5012
5013class SetAgg(AggFunc):
5014    pass
5015
5016
5017class SHA(Func):
5018    _sql_names = ["SHA", "SHA1"]
5019
5020
5021class SHA2(Func):
5022    _sql_names = ["SHA2"]
5023    arg_types = {"this": True, "length": False}
5024
5025
5026class SortArray(Func):
5027    arg_types = {"this": True, "asc": False}
5028
5029
5030class Split(Func):
5031    arg_types = {"this": True, "expression": True, "limit": False}
5032
5033
5034# Start may be omitted in the case of postgres
5035# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5036class Substring(Func):
5037    arg_types = {"this": True, "start": False, "length": False}
5038
5039
5040class StandardHash(Func):
5041    arg_types = {"this": True, "expression": False}
5042
5043
5044class StartsWith(Func):
5045    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5046    arg_types = {"this": True, "expression": True}
5047
5048
5049class StrPosition(Func):
5050    arg_types = {
5051        "this": True,
5052        "substr": True,
5053        "position": False,
5054        "instance": False,
5055    }
5056
5057
5058class StrToDate(Func):
5059    arg_types = {"this": True, "format": True}
5060
5061
5062class StrToTime(Func):
5063    arg_types = {"this": True, "format": True, "zone": False}
5064
5065
5066# Spark allows unix_timestamp()
5067# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5068class StrToUnix(Func):
5069    arg_types = {"this": False, "format": False}
5070
5071
5072# https://prestodb.io/docs/current/functions/string.html
5073# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5074class StrToMap(Func):
5075    arg_types = {
5076        "this": True,
5077        "pair_delim": False,
5078        "key_value_delim": False,
5079        "duplicate_resolution_callback": False,
5080    }
5081
5082
5083class NumberToStr(Func):
5084    arg_types = {"this": True, "format": True, "culture": False}
5085
5086
5087class FromBase(Func):
5088    arg_types = {"this": True, "expression": True}
5089
5090
5091class Struct(Func):
5092    arg_types = {"expressions": False}
5093    is_var_len_args = True
5094
5095
5096class StructExtract(Func):
5097    arg_types = {"this": True, "expression": True}
5098
5099
5100# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5101# https://docs.snowflake.com/en/sql-reference/functions/insert
5102class Stuff(Func):
5103    _sql_names = ["STUFF", "INSERT"]
5104    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5105
5106
5107class Sum(AggFunc):
5108    pass
5109
5110
5111class Sqrt(Func):
5112    pass
5113
5114
5115class Stddev(AggFunc):
5116    pass
5117
5118
5119class StddevPop(AggFunc):
5120    pass
5121
5122
5123class StddevSamp(AggFunc):
5124    pass
5125
5126
5127class TimeToStr(Func):
5128    arg_types = {"this": True, "format": True, "culture": False}
5129
5130
5131class TimeToTimeStr(Func):
5132    pass
5133
5134
5135class TimeToUnix(Func):
5136    pass
5137
5138
5139class TimeStrToDate(Func):
5140    pass
5141
5142
5143class TimeStrToTime(Func):
5144    pass
5145
5146
5147class TimeStrToUnix(Func):
5148    pass
5149
5150
5151class Trim(Func):
5152    arg_types = {
5153        "this": True,
5154        "expression": False,
5155        "position": False,
5156        "collation": False,
5157    }
5158
5159
5160class TsOrDsAdd(Func, TimeUnit):
5161    arg_types = {"this": True, "expression": True, "unit": False}
5162
5163
5164class TsOrDsToDateStr(Func):
5165    pass
5166
5167
5168class TsOrDsToDate(Func):
5169    arg_types = {"this": True, "format": False}
5170
5171
5172class TsOrDiToDi(Func):
5173    pass
5174
5175
5176class Unhex(Func):
5177    pass
5178
5179
5180class UnixToStr(Func):
5181    arg_types = {"this": True, "format": False}
5182
5183
5184# https://prestodb.io/docs/current/functions/datetime.html
5185# presto has weird zone/hours/minutes
5186class UnixToTime(Func):
5187    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5188
5189    SECONDS = Literal.string("seconds")
5190    MILLIS = Literal.string("millis")
5191    MICROS = Literal.string("micros")
5192
5193
5194class UnixToTimeStr(Func):
5195    pass
5196
5197
5198class Upper(Func):
5199    _sql_names = ["UPPER", "UCASE"]
5200
5201
5202class Variance(AggFunc):
5203    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5204
5205
5206class VariancePop(AggFunc):
5207    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5208
5209
5210class Week(Func):
5211    arg_types = {"this": True, "mode": False}
5212
5213
5214class XMLTable(Func):
5215    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5216
5217
5218class Year(Func):
5219    pass
5220
5221
5222class Use(Expression):
5223    arg_types = {"this": True, "kind": False}
5224
5225
5226class Merge(Expression):
5227    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5228
5229
5230class When(Func):
5231    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5232
5233
5234# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5235# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5236class NextValueFor(Func):
5237    arg_types = {"this": True, "order": False}
5238
5239
5240def _norm_arg(arg):
5241    return arg.lower() if type(arg) is str else arg
5242
5243
5244ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5245
5246
5247# Helpers
5248@t.overload
5249def maybe_parse(
5250    sql_or_expression: ExpOrStr,
5251    *,
5252    into: t.Type[E],
5253    dialect: DialectType = None,
5254    prefix: t.Optional[str] = None,
5255    copy: bool = False,
5256    **opts,
5257) -> E:
5258    ...
5259
5260
5261@t.overload
5262def maybe_parse(
5263    sql_or_expression: str | E,
5264    *,
5265    into: t.Optional[IntoType] = None,
5266    dialect: DialectType = None,
5267    prefix: t.Optional[str] = None,
5268    copy: bool = False,
5269    **opts,
5270) -> E:
5271    ...
5272
5273
5274def maybe_parse(
5275    sql_or_expression: ExpOrStr,
5276    *,
5277    into: t.Optional[IntoType] = None,
5278    dialect: DialectType = None,
5279    prefix: t.Optional[str] = None,
5280    copy: bool = False,
5281    **opts,
5282) -> Expression:
5283    """Gracefully handle a possible string or expression.
5284
5285    Example:
5286        >>> maybe_parse("1")
5287        (LITERAL this: 1, is_string: False)
5288        >>> maybe_parse(to_identifier("x"))
5289        (IDENTIFIER this: x, quoted: False)
5290
5291    Args:
5292        sql_or_expression: the SQL code string or an expression
5293        into: the SQLGlot Expression to parse into
5294        dialect: the dialect used to parse the input expressions (in the case that an
5295            input expression is a SQL string).
5296        prefix: a string to prefix the sql with before it gets parsed
5297            (automatically includes a space)
5298        copy: whether or not to copy the expression.
5299        **opts: other options to use to parse the input expressions (again, in the case
5300            that an input expression is a SQL string).
5301
5302    Returns:
5303        Expression: the parsed or given expression.
5304    """
5305    if isinstance(sql_or_expression, Expression):
5306        if copy:
5307            return sql_or_expression.copy()
5308        return sql_or_expression
5309
5310    if sql_or_expression is None:
5311        raise ParseError(f"SQL cannot be None")
5312
5313    import sqlglot
5314
5315    sql = str(sql_or_expression)
5316    if prefix:
5317        sql = f"{prefix} {sql}"
5318
5319    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5320
5321
5322@t.overload
5323def maybe_copy(instance: None, copy: bool = True) -> None:
5324    ...
5325
5326
5327@t.overload
5328def maybe_copy(instance: E, copy: bool = True) -> E:
5329    ...
5330
5331
5332def maybe_copy(instance, copy=True):
5333    return instance.copy() if copy and instance else instance
5334
5335
5336def _is_wrong_expression(expression, into):
5337    return isinstance(expression, Expression) and not isinstance(expression, into)
5338
5339
5340def _apply_builder(
5341    expression,
5342    instance,
5343    arg,
5344    copy=True,
5345    prefix=None,
5346    into=None,
5347    dialect=None,
5348    into_arg="this",
5349    **opts,
5350):
5351    if _is_wrong_expression(expression, into):
5352        expression = into(**{into_arg: expression})
5353    instance = maybe_copy(instance, copy)
5354    expression = maybe_parse(
5355        sql_or_expression=expression,
5356        prefix=prefix,
5357        into=into,
5358        dialect=dialect,
5359        **opts,
5360    )
5361    instance.set(arg, expression)
5362    return instance
5363
5364
5365def _apply_child_list_builder(
5366    *expressions,
5367    instance,
5368    arg,
5369    append=True,
5370    copy=True,
5371    prefix=None,
5372    into=None,
5373    dialect=None,
5374    properties=None,
5375    **opts,
5376):
5377    instance = maybe_copy(instance, copy)
5378    parsed = []
5379    for expression in expressions:
5380        if expression is not None:
5381            if _is_wrong_expression(expression, into):
5382                expression = into(expressions=[expression])
5383
5384            expression = maybe_parse(
5385                expression,
5386                into=into,
5387                dialect=dialect,
5388                prefix=prefix,
5389                **opts,
5390            )
5391            parsed.extend(expression.expressions)
5392
5393    existing = instance.args.get(arg)
5394    if append and existing:
5395        parsed = existing.expressions + parsed
5396
5397    child = into(expressions=parsed)
5398    for k, v in (properties or {}).items():
5399        child.set(k, v)
5400    instance.set(arg, child)
5401
5402    return instance
5403
5404
5405def _apply_list_builder(
5406    *expressions,
5407    instance,
5408    arg,
5409    append=True,
5410    copy=True,
5411    prefix=None,
5412    into=None,
5413    dialect=None,
5414    **opts,
5415):
5416    inst = maybe_copy(instance, copy)
5417
5418    expressions = [
5419        maybe_parse(
5420            sql_or_expression=expression,
5421            into=into,
5422            prefix=prefix,
5423            dialect=dialect,
5424            **opts,
5425        )
5426        for expression in expressions
5427        if expression is not None
5428    ]
5429
5430    existing_expressions = inst.args.get(arg)
5431    if append and existing_expressions:
5432        expressions = existing_expressions + expressions
5433
5434    inst.set(arg, expressions)
5435    return inst
5436
5437
5438def _apply_conjunction_builder(
5439    *expressions,
5440    instance,
5441    arg,
5442    into=None,
5443    append=True,
5444    copy=True,
5445    dialect=None,
5446    **opts,
5447):
5448    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5449    if not expressions:
5450        return instance
5451
5452    inst = maybe_copy(instance, copy)
5453
5454    existing = inst.args.get(arg)
5455    if append and existing is not None:
5456        expressions = [existing.this if into else existing] + list(expressions)
5457
5458    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5459
5460    inst.set(arg, into(this=node) if into else node)
5461    return inst
5462
5463
5464def _apply_cte_builder(
5465    instance: E,
5466    alias: ExpOrStr,
5467    as_: ExpOrStr,
5468    recursive: t.Optional[bool] = None,
5469    append: bool = True,
5470    dialect: DialectType = None,
5471    copy: bool = True,
5472    **opts,
5473) -> E:
5474    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5475    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5476    cte = CTE(this=as_expression, alias=alias_expression)
5477    return _apply_child_list_builder(
5478        cte,
5479        instance=instance,
5480        arg="with",
5481        append=append,
5482        copy=copy,
5483        into=With,
5484        properties={"recursive": recursive or False},
5485    )
5486
5487
5488def _combine(
5489    expressions: t.Sequence[t.Optional[ExpOrStr]],
5490    operator: t.Type[Connector],
5491    dialect: DialectType = None,
5492    copy: bool = True,
5493    **opts,
5494) -> Expression:
5495    conditions = [
5496        condition(expression, dialect=dialect, copy=copy, **opts)
5497        for expression in expressions
5498        if expression is not None
5499    ]
5500
5501    this, *rest = conditions
5502    if rest:
5503        this = _wrap(this, Connector)
5504    for expression in rest:
5505        this = operator(this=this, expression=_wrap(expression, Connector))
5506
5507    return this
5508
5509
5510def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5511    return Paren(this=expression) if isinstance(expression, kind) else expression
5512
5513
5514def union(
5515    left: ExpOrStr,
5516    right: ExpOrStr,
5517    distinct: bool = True,
5518    dialect: DialectType = None,
5519    copy: bool = True,
5520    **opts,
5521) -> Union:
5522    """
5523    Initializes a syntax tree from one UNION expression.
5524
5525    Example:
5526        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5527        'SELECT * FROM foo UNION SELECT * FROM bla'
5528
5529    Args:
5530        left: the SQL code string corresponding to the left-hand side.
5531            If an `Expression` instance is passed, it will be used as-is.
5532        right: the SQL code string corresponding to the right-hand side.
5533            If an `Expression` instance is passed, it will be used as-is.
5534        distinct: set the DISTINCT flag if and only if this is true.
5535        dialect: the dialect used to parse the input expression.
5536        copy: whether or not to copy the expression.
5537        opts: other options to use to parse the input expressions.
5538
5539    Returns:
5540        The new Union instance.
5541    """
5542    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5543    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5544
5545    return Union(this=left, expression=right, distinct=distinct)
5546
5547
5548def intersect(
5549    left: ExpOrStr,
5550    right: ExpOrStr,
5551    distinct: bool = True,
5552    dialect: DialectType = None,
5553    copy: bool = True,
5554    **opts,
5555) -> Intersect:
5556    """
5557    Initializes a syntax tree from one INTERSECT expression.
5558
5559    Example:
5560        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5561        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5562
5563    Args:
5564        left: the SQL code string corresponding to the left-hand side.
5565            If an `Expression` instance is passed, it will be used as-is.
5566        right: the SQL code string corresponding to the right-hand side.
5567            If an `Expression` instance is passed, it will be used as-is.
5568        distinct: set the DISTINCT flag if and only if this is true.
5569        dialect: the dialect used to parse the input expression.
5570        copy: whether or not to copy the expression.
5571        opts: other options to use to parse the input expressions.
5572
5573    Returns:
5574        The new Intersect instance.
5575    """
5576    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5577    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5578
5579    return Intersect(this=left, expression=right, distinct=distinct)
5580
5581
5582def except_(
5583    left: ExpOrStr,
5584    right: ExpOrStr,
5585    distinct: bool = True,
5586    dialect: DialectType = None,
5587    copy: bool = True,
5588    **opts,
5589) -> Except:
5590    """
5591    Initializes a syntax tree from one EXCEPT expression.
5592
5593    Example:
5594        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5595        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5596
5597    Args:
5598        left: the SQL code string corresponding to the left-hand side.
5599            If an `Expression` instance is passed, it will be used as-is.
5600        right: the SQL code string corresponding to the right-hand side.
5601            If an `Expression` instance is passed, it will be used as-is.
5602        distinct: set the DISTINCT flag if and only if this is true.
5603        dialect: the dialect used to parse the input expression.
5604        copy: whether or not to copy the expression.
5605        opts: other options to use to parse the input expressions.
5606
5607    Returns:
5608        The new Except instance.
5609    """
5610    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5611    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5612
5613    return Except(this=left, expression=right, distinct=distinct)
5614
5615
5616def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5617    """
5618    Initializes a syntax tree from one or multiple SELECT expressions.
5619
5620    Example:
5621        >>> select("col1", "col2").from_("tbl").sql()
5622        'SELECT col1, col2 FROM tbl'
5623
5624    Args:
5625        *expressions: the SQL code string to parse as the expressions of a
5626            SELECT statement. If an Expression instance is passed, this is used as-is.
5627        dialect: the dialect used to parse the input expressions (in the case that an
5628            input expression is a SQL string).
5629        **opts: other options to use to parse the input expressions (again, in the case
5630            that an input expression is a SQL string).
5631
5632    Returns:
5633        Select: the syntax tree for the SELECT statement.
5634    """
5635    return Select().select(*expressions, dialect=dialect, **opts)
5636
5637
5638def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5639    """
5640    Initializes a syntax tree from a FROM expression.
5641
5642    Example:
5643        >>> from_("tbl").select("col1", "col2").sql()
5644        'SELECT col1, col2 FROM tbl'
5645
5646    Args:
5647        *expression: the SQL code string to parse as the FROM expressions of a
5648            SELECT statement. If an Expression instance is passed, this is used as-is.
5649        dialect: the dialect used to parse the input expression (in the case that the
5650            input expression is a SQL string).
5651        **opts: other options to use to parse the input expressions (again, in the case
5652            that the input expression is a SQL string).
5653
5654    Returns:
5655        Select: the syntax tree for the SELECT statement.
5656    """
5657    return Select().from_(expression, dialect=dialect, **opts)
5658
5659
5660def update(
5661    table: str | Table,
5662    properties: dict,
5663    where: t.Optional[ExpOrStr] = None,
5664    from_: t.Optional[ExpOrStr] = None,
5665    dialect: DialectType = None,
5666    **opts,
5667) -> Update:
5668    """
5669    Creates an update statement.
5670
5671    Example:
5672        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5673        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5674
5675    Args:
5676        *properties: dictionary of properties to set which are
5677            auto converted to sql objects eg None -> NULL
5678        where: sql conditional parsed into a WHERE statement
5679        from_: sql statement parsed into a FROM statement
5680        dialect: the dialect used to parse the input expressions.
5681        **opts: other options to use to parse the input expressions.
5682
5683    Returns:
5684        Update: the syntax tree for the UPDATE statement.
5685    """
5686    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5687    update_expr.set(
5688        "expressions",
5689        [
5690            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5691            for k, v in properties.items()
5692        ],
5693    )
5694    if from_:
5695        update_expr.set(
5696            "from",
5697            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5698        )
5699    if isinstance(where, Condition):
5700        where = Where(this=where)
5701    if where:
5702        update_expr.set(
5703            "where",
5704            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5705        )
5706    return update_expr
5707
5708
5709def delete(
5710    table: ExpOrStr,
5711    where: t.Optional[ExpOrStr] = None,
5712    returning: t.Optional[ExpOrStr] = None,
5713    dialect: DialectType = None,
5714    **opts,
5715) -> Delete:
5716    """
5717    Builds a delete statement.
5718
5719    Example:
5720        >>> delete("my_table", where="id > 1").sql()
5721        'DELETE FROM my_table WHERE id > 1'
5722
5723    Args:
5724        where: sql conditional parsed into a WHERE statement
5725        returning: sql conditional parsed into a RETURNING statement
5726        dialect: the dialect used to parse the input expressions.
5727        **opts: other options to use to parse the input expressions.
5728
5729    Returns:
5730        Delete: the syntax tree for the DELETE statement.
5731    """
5732    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5733    if where:
5734        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5735    if returning:
5736        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5737    return delete_expr
5738
5739
5740def insert(
5741    expression: ExpOrStr,
5742    into: ExpOrStr,
5743    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5744    overwrite: t.Optional[bool] = None,
5745    dialect: DialectType = None,
5746    copy: bool = True,
5747    **opts,
5748) -> Insert:
5749    """
5750    Builds an INSERT statement.
5751
5752    Example:
5753        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5754        'INSERT INTO tbl VALUES (1, 2, 3)'
5755
5756    Args:
5757        expression: the sql string or expression of the INSERT statement
5758        into: the tbl to insert data to.
5759        columns: optionally the table's column names.
5760        overwrite: whether to INSERT OVERWRITE or not.
5761        dialect: the dialect used to parse the input expressions.
5762        copy: whether or not to copy the expression.
5763        **opts: other options to use to parse the input expressions.
5764
5765    Returns:
5766        Insert: the syntax tree for the INSERT statement.
5767    """
5768    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5769    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5770
5771    if columns:
5772        this = _apply_list_builder(
5773            *columns,
5774            instance=Schema(this=this),
5775            arg="expressions",
5776            into=Identifier,
5777            copy=False,
5778            dialect=dialect,
5779            **opts,
5780        )
5781
5782    return Insert(this=this, expression=expr, overwrite=overwrite)
5783
5784
5785def condition(
5786    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5787) -> Condition:
5788    """
5789    Initialize a logical condition expression.
5790
5791    Example:
5792        >>> condition("x=1").sql()
5793        'x = 1'
5794
5795        This is helpful for composing larger logical syntax trees:
5796        >>> where = condition("x=1")
5797        >>> where = where.and_("y=1")
5798        >>> Select().from_("tbl").select("*").where(where).sql()
5799        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5800
5801    Args:
5802        *expression: the SQL code string to parse.
5803            If an Expression instance is passed, this is used as-is.
5804        dialect: the dialect used to parse the input expression (in the case that the
5805            input expression is a SQL string).
5806        copy: Whether or not to copy `expression` (only applies to expressions).
5807        **opts: other options to use to parse the input expressions (again, in the case
5808            that the input expression is a SQL string).
5809
5810    Returns:
5811        The new Condition instance
5812    """
5813    return maybe_parse(
5814        expression,
5815        into=Condition,
5816        dialect=dialect,
5817        copy=copy,
5818        **opts,
5819    )
5820
5821
5822def and_(
5823    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5824) -> Condition:
5825    """
5826    Combine multiple conditions with an AND logical operator.
5827
5828    Example:
5829        >>> and_("x=1", and_("y=1", "z=1")).sql()
5830        'x = 1 AND (y = 1 AND z = 1)'
5831
5832    Args:
5833        *expressions: the SQL code strings to parse.
5834            If an Expression instance is passed, this is used as-is.
5835        dialect: the dialect used to parse the input expression.
5836        copy: whether or not to copy `expressions` (only applies to Expressions).
5837        **opts: other options to use to parse the input expressions.
5838
5839    Returns:
5840        And: the new condition
5841    """
5842    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5843
5844
5845def or_(
5846    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5847) -> Condition:
5848    """
5849    Combine multiple conditions with an OR logical operator.
5850
5851    Example:
5852        >>> or_("x=1", or_("y=1", "z=1")).sql()
5853        'x = 1 OR (y = 1 OR z = 1)'
5854
5855    Args:
5856        *expressions: the SQL code strings to parse.
5857            If an Expression instance is passed, this is used as-is.
5858        dialect: the dialect used to parse the input expression.
5859        copy: whether or not to copy `expressions` (only applies to Expressions).
5860        **opts: other options to use to parse the input expressions.
5861
5862    Returns:
5863        Or: the new condition
5864    """
5865    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5866
5867
5868def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5869    """
5870    Wrap a condition with a NOT operator.
5871
5872    Example:
5873        >>> not_("this_suit='black'").sql()
5874        "NOT this_suit = 'black'"
5875
5876    Args:
5877        expression: the SQL code string to parse.
5878            If an Expression instance is passed, this is used as-is.
5879        dialect: the dialect used to parse the input expression.
5880        copy: whether to copy the expression or not.
5881        **opts: other options to use to parse the input expressions.
5882
5883    Returns:
5884        The new condition.
5885    """
5886    this = condition(
5887        expression,
5888        dialect=dialect,
5889        copy=copy,
5890        **opts,
5891    )
5892    return Not(this=_wrap(this, Connector))
5893
5894
5895def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5896    """
5897    Wrap an expression in parentheses.
5898
5899    Example:
5900        >>> paren("5 + 3").sql()
5901        '(5 + 3)'
5902
5903    Args:
5904        expression: the SQL code string to parse.
5905            If an Expression instance is passed, this is used as-is.
5906        copy: whether to copy the expression or not.
5907
5908    Returns:
5909        The wrapped expression.
5910    """
5911    return Paren(this=maybe_parse(expression, copy=copy))
5912
5913
5914SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5915
5916
5917@t.overload
5918def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5919    ...
5920
5921
5922@t.overload
5923def to_identifier(
5924    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5925) -> Identifier:
5926    ...
5927
5928
5929def to_identifier(name, quoted=None, copy=True):
5930    """Builds an identifier.
5931
5932    Args:
5933        name: The name to turn into an identifier.
5934        quoted: Whether or not force quote the identifier.
5935        copy: Whether or not to copy name if it's an Identifier.
5936
5937    Returns:
5938        The identifier ast node.
5939    """
5940
5941    if name is None:
5942        return None
5943
5944    if isinstance(name, Identifier):
5945        identifier = maybe_copy(name, copy)
5946    elif isinstance(name, str):
5947        identifier = Identifier(
5948            this=name,
5949            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5950        )
5951    else:
5952        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5953    return identifier
5954
5955
5956def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
5957    """
5958    Parses a given string into an identifier.
5959
5960    Args:
5961        name: The name to parse into an identifier.
5962        dialect: The dialect to parse against.
5963
5964    Returns:
5965        The identifier ast node.
5966    """
5967    try:
5968        expression = maybe_parse(name, dialect=dialect, into=Identifier)
5969    except ParseError:
5970        expression = to_identifier(name)
5971
5972    return expression
5973
5974
5975INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5976
5977
5978def to_interval(interval: str | Literal) -> Interval:
5979    """Builds an interval expression from a string like '1 day' or '5 months'."""
5980    if isinstance(interval, Literal):
5981        if not interval.is_string:
5982            raise ValueError("Invalid interval string.")
5983
5984        interval = interval.this
5985
5986    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5987
5988    if not interval_parts:
5989        raise ValueError("Invalid interval string.")
5990
5991    return Interval(
5992        this=Literal.string(interval_parts.group(1)),
5993        unit=Var(this=interval_parts.group(2)),
5994    )
5995
5996
5997@t.overload
5998def to_table(sql_path: str | Table, **kwargs) -> Table:
5999    ...
6000
6001
6002@t.overload
6003def to_table(sql_path: None, **kwargs) -> None:
6004    ...
6005
6006
6007def to_table(
6008    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
6009) -> t.Optional[Table]:
6010    """
6011    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6012    If a table is passed in then that table is returned.
6013
6014    Args:
6015        sql_path: a `[catalog].[schema].[table]` string.
6016        dialect: the source dialect according to which the table name will be parsed.
6017        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6018
6019    Returns:
6020        A table expression.
6021    """
6022    if sql_path is None or isinstance(sql_path, Table):
6023        return sql_path
6024    if not isinstance(sql_path, str):
6025        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6026
6027    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6028    if table:
6029        for k, v in kwargs.items():
6030            table.set(k, v)
6031
6032    return table
6033
6034
6035def to_column(sql_path: str | Column, **kwargs) -> Column:
6036    """
6037    Create a column from a `[table].[column]` sql path. Schema is optional.
6038
6039    If a column is passed in then that column is returned.
6040
6041    Args:
6042        sql_path: `[table].[column]` string
6043    Returns:
6044        Table: A column expression
6045    """
6046    if sql_path is None or isinstance(sql_path, Column):
6047        return sql_path
6048    if not isinstance(sql_path, str):
6049        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6050    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6051
6052
6053def alias_(
6054    expression: ExpOrStr,
6055    alias: str | Identifier,
6056    table: bool | t.Sequence[str | Identifier] = False,
6057    quoted: t.Optional[bool] = None,
6058    dialect: DialectType = None,
6059    copy: bool = True,
6060    **opts,
6061):
6062    """Create an Alias expression.
6063
6064    Example:
6065        >>> alias_('foo', 'bar').sql()
6066        'foo AS bar'
6067
6068        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6069        '(SELECT 1, 2) AS bar(a, b)'
6070
6071    Args:
6072        expression: the SQL code strings to parse.
6073            If an Expression instance is passed, this is used as-is.
6074        alias: the alias name to use. If the name has
6075            special characters it is quoted.
6076        table: Whether or not to create a table alias, can also be a list of columns.
6077        quoted: whether or not to quote the alias
6078        dialect: the dialect used to parse the input expression.
6079        copy: Whether or not to copy the expression.
6080        **opts: other options to use to parse the input expressions.
6081
6082    Returns:
6083        Alias: the aliased expression
6084    """
6085    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6086    alias = to_identifier(alias, quoted=quoted)
6087
6088    if table:
6089        table_alias = TableAlias(this=alias)
6090        exp.set("alias", table_alias)
6091
6092        if not isinstance(table, bool):
6093            for column in table:
6094                table_alias.append("columns", to_identifier(column, quoted=quoted))
6095
6096        return exp
6097
6098    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6099    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6100    # for the complete Window expression.
6101    #
6102    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6103
6104    if "alias" in exp.arg_types and not isinstance(exp, Window):
6105        exp.set("alias", alias)
6106        return exp
6107    return Alias(this=exp, alias=alias)
6108
6109
6110def subquery(
6111    expression: ExpOrStr,
6112    alias: t.Optional[Identifier | str] = None,
6113    dialect: DialectType = None,
6114    **opts,
6115) -> Select:
6116    """
6117    Build a subquery expression.
6118
6119    Example:
6120        >>> subquery('select x from tbl', 'bar').select('x').sql()
6121        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6122
6123    Args:
6124        expression: the SQL code strings to parse.
6125            If an Expression instance is passed, this is used as-is.
6126        alias: the alias name to use.
6127        dialect: the dialect used to parse the input expression.
6128        **opts: other options to use to parse the input expressions.
6129
6130    Returns:
6131        A new Select instance with the subquery expression included.
6132    """
6133
6134    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6135    return Select().from_(expression, dialect=dialect, **opts)
6136
6137
6138def column(
6139    col: str | Identifier,
6140    table: t.Optional[str | Identifier] = None,
6141    db: t.Optional[str | Identifier] = None,
6142    catalog: t.Optional[str | Identifier] = None,
6143    quoted: t.Optional[bool] = None,
6144) -> Column:
6145    """
6146    Build a Column.
6147
6148    Args:
6149        col: Column name.
6150        table: Table name.
6151        db: Database name.
6152        catalog: Catalog name.
6153        quoted: Whether to force quotes on the column's identifiers.
6154
6155    Returns:
6156        The new Column instance.
6157    """
6158    return Column(
6159        this=to_identifier(col, quoted=quoted),
6160        table=to_identifier(table, quoted=quoted),
6161        db=to_identifier(db, quoted=quoted),
6162        catalog=to_identifier(catalog, quoted=quoted),
6163    )
6164
6165
6166def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6167    """Cast an expression to a data type.
6168
6169    Example:
6170        >>> cast('x + 1', 'int').sql()
6171        'CAST(x + 1 AS INT)'
6172
6173    Args:
6174        expression: The expression to cast.
6175        to: The datatype to cast to.
6176
6177    Returns:
6178        The new Cast instance.
6179    """
6180    expression = maybe_parse(expression, **opts)
6181    data_type = DataType.build(to, **opts)
6182    expression = Cast(this=expression, to=data_type)
6183    expression.type = data_type
6184    return expression
6185
6186
6187def table_(
6188    table: Identifier | str,
6189    db: t.Optional[Identifier | str] = None,
6190    catalog: t.Optional[Identifier | str] = None,
6191    quoted: t.Optional[bool] = None,
6192    alias: t.Optional[Identifier | str] = None,
6193) -> Table:
6194    """Build a Table.
6195
6196    Args:
6197        table: Table name.
6198        db: Database name.
6199        catalog: Catalog name.
6200        quote: Whether to force quotes on the table's identifiers.
6201        alias: Table's alias.
6202
6203    Returns:
6204        The new Table instance.
6205    """
6206    return Table(
6207        this=to_identifier(table, quoted=quoted) if table else None,
6208        db=to_identifier(db, quoted=quoted) if db else None,
6209        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6210        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6211    )
6212
6213
6214def values(
6215    values: t.Iterable[t.Tuple[t.Any, ...]],
6216    alias: t.Optional[str] = None,
6217    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6218) -> Values:
6219    """Build VALUES statement.
6220
6221    Example:
6222        >>> values([(1, '2')]).sql()
6223        "VALUES (1, '2')"
6224
6225    Args:
6226        values: values statements that will be converted to SQL
6227        alias: optional alias
6228        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6229         If either are provided then an alias is also required.
6230
6231    Returns:
6232        Values: the Values expression object
6233    """
6234    if columns and not alias:
6235        raise ValueError("Alias is required when providing columns")
6236
6237    return Values(
6238        expressions=[convert(tup) for tup in values],
6239        alias=(
6240            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6241            if columns
6242            else (TableAlias(this=to_identifier(alias)) if alias else None)
6243        ),
6244    )
6245
6246
6247def var(name: t.Optional[ExpOrStr]) -> Var:
6248    """Build a SQL variable.
6249
6250    Example:
6251        >>> repr(var('x'))
6252        '(VAR this: x)'
6253
6254        >>> repr(var(column('x', table='y')))
6255        '(VAR this: x)'
6256
6257    Args:
6258        name: The name of the var or an expression who's name will become the var.
6259
6260    Returns:
6261        The new variable node.
6262    """
6263    if not name:
6264        raise ValueError("Cannot convert empty name into var.")
6265
6266    if isinstance(name, Expression):
6267        name = name.name
6268    return Var(this=name)
6269
6270
6271def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6272    """Build ALTER TABLE... RENAME... expression
6273
6274    Args:
6275        old_name: The old name of the table
6276        new_name: The new name of the table
6277
6278    Returns:
6279        Alter table expression
6280    """
6281    old_table = to_table(old_name)
6282    new_table = to_table(new_name)
6283    return AlterTable(
6284        this=old_table,
6285        actions=[
6286            RenameTable(this=new_table),
6287        ],
6288    )
6289
6290
6291def convert(value: t.Any, copy: bool = False) -> Expression:
6292    """Convert a python value into an expression object.
6293
6294    Raises an error if a conversion is not possible.
6295
6296    Args:
6297        value: A python object.
6298        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6299
6300    Returns:
6301        Expression: the equivalent expression object.
6302    """
6303    if isinstance(value, Expression):
6304        return maybe_copy(value, copy)
6305    if isinstance(value, str):
6306        return Literal.string(value)
6307    if isinstance(value, bool):
6308        return Boolean(this=value)
6309    if value is None or (isinstance(value, float) and math.isnan(value)):
6310        return NULL
6311    if isinstance(value, numbers.Number):
6312        return Literal.number(value)
6313    if isinstance(value, datetime.datetime):
6314        datetime_literal = Literal.string(
6315            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6316        )
6317        return TimeStrToTime(this=datetime_literal)
6318    if isinstance(value, datetime.date):
6319        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6320        return DateStrToDate(this=date_literal)
6321    if isinstance(value, tuple):
6322        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6323    if isinstance(value, list):
6324        return Array(expressions=[convert(v, copy=copy) for v in value])
6325    if isinstance(value, dict):
6326        return Map(
6327            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6328            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6329        )
6330    raise ValueError(f"Cannot convert {value}")
6331
6332
6333def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6334    """
6335    Replace children of an expression with the result of a lambda fun(child) -> exp.
6336    """
6337    for k, v in expression.args.items():
6338        is_list_arg = type(v) is list
6339
6340        child_nodes = v if is_list_arg else [v]
6341        new_child_nodes = []
6342
6343        for cn in child_nodes:
6344            if isinstance(cn, Expression):
6345                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6346                    new_child_nodes.append(child_node)
6347                    child_node.parent = expression
6348                    child_node.arg_key = k
6349            else:
6350                new_child_nodes.append(cn)
6351
6352        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6353
6354
6355def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6356    """
6357    Return all table names referenced through columns in an expression.
6358
6359    Example:
6360        >>> import sqlglot
6361        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6362        ['a', 'c']
6363
6364    Args:
6365        expression: expression to find table names.
6366        exclude: a table name to exclude
6367
6368    Returns:
6369        A list of unique names.
6370    """
6371    return {
6372        table
6373        for table in (column.table for column in expression.find_all(Column))
6374        if table and table != exclude
6375    }
6376
6377
6378def table_name(table: Table | str, dialect: DialectType = None) -> str:
6379    """Get the full name of a table as a string.
6380
6381    Args:
6382        table: Table expression node or string.
6383        dialect: The dialect to generate the table name for.
6384
6385    Examples:
6386        >>> from sqlglot import exp, parse_one
6387        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6388        'a.b.c'
6389
6390    Returns:
6391        The table name.
6392    """
6393
6394    table = maybe_parse(table, into=Table, dialect=dialect)
6395
6396    if not table:
6397        raise ValueError(f"Cannot parse {table}")
6398
6399    return ".".join(
6400        part.sql(dialect=dialect, identify=True)
6401        if not SAFE_IDENTIFIER_RE.match(part.name)
6402        else part.name
6403        for part in table.parts
6404    )
6405
6406
6407def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6408    """Replace all tables in expression according to the mapping.
6409
6410    Args:
6411        expression: expression node to be transformed and replaced.
6412        mapping: mapping of table names.
6413        copy: whether or not to copy the expression.
6414
6415    Examples:
6416        >>> from sqlglot import exp, parse_one
6417        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6418        'SELECT * FROM c'
6419
6420    Returns:
6421        The mapped expression.
6422    """
6423
6424    def _replace_tables(node: Expression) -> Expression:
6425        if isinstance(node, Table):
6426            new_name = mapping.get(table_name(node))
6427            if new_name:
6428                return to_table(
6429                    new_name,
6430                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6431                )
6432        return node
6433
6434    return expression.transform(_replace_tables, copy=copy)
6435
6436
6437def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6438    """Replace placeholders in an expression.
6439
6440    Args:
6441        expression: expression node to be transformed and replaced.
6442        args: positional names that will substitute unnamed placeholders in the given order.
6443        kwargs: keyword arguments that will substitute named placeholders.
6444
6445    Examples:
6446        >>> from sqlglot import exp, parse_one
6447        >>> replace_placeholders(
6448        ...     parse_one("select * from :tbl where ? = ?"),
6449        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6450        ... ).sql()
6451        "SELECT * FROM foo WHERE str_col = 'b'"
6452
6453    Returns:
6454        The mapped expression.
6455    """
6456
6457    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6458        if isinstance(node, Placeholder):
6459            if node.name:
6460                new_name = kwargs.get(node.name)
6461                if new_name:
6462                    return convert(new_name)
6463            else:
6464                try:
6465                    return convert(next(args))
6466                except StopIteration:
6467                    pass
6468        return node
6469
6470    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6471
6472
6473def expand(
6474    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6475) -> Expression:
6476    """Transforms an expression by expanding all referenced sources into subqueries.
6477
6478    Examples:
6479        >>> from sqlglot import parse_one
6480        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6481        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6482
6483        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6484        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6485
6486    Args:
6487        expression: The expression to expand.
6488        sources: A dictionary of name to Subqueryables.
6489        copy: Whether or not to copy the expression during transformation. Defaults to True.
6490
6491    Returns:
6492        The transformed expression.
6493    """
6494
6495    def _expand(node: Expression):
6496        if isinstance(node, Table):
6497            name = table_name(node)
6498            source = sources.get(name)
6499            if source:
6500                subquery = source.subquery(node.alias or name)
6501                subquery.comments = [f"source: {name}"]
6502                return subquery.transform(_expand, copy=False)
6503        return node
6504
6505    return expression.transform(_expand, copy=copy)
6506
6507
6508def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6509    """
6510    Returns a Func expression.
6511
6512    Examples:
6513        >>> func("abs", 5).sql()
6514        'ABS(5)'
6515
6516        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6517        'CAST(5 AS DOUBLE)'
6518
6519    Args:
6520        name: the name of the function to build.
6521        args: the args used to instantiate the function of interest.
6522        dialect: the source dialect.
6523        kwargs: the kwargs used to instantiate the function of interest.
6524
6525    Note:
6526        The arguments `args` and `kwargs` are mutually exclusive.
6527
6528    Returns:
6529        An instance of the function of interest, or an anonymous function, if `name` doesn't
6530        correspond to an existing `sqlglot.expressions.Func` class.
6531    """
6532    if args and kwargs:
6533        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6534
6535    from sqlglot.dialects.dialect import Dialect
6536
6537    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6538    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6539
6540    parser = Dialect.get_or_raise(dialect)().parser()
6541    from_args_list = parser.FUNCTIONS.get(name.upper())
6542
6543    if from_args_list:
6544        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6545    else:
6546        kwargs = kwargs or {"expressions": converted}
6547        function = Anonymous(this=name, **kwargs)
6548
6549    for error_message in function.error_messages(converted):
6550        raise ValueError(error_message)
6551
6552    return function
6553
6554
6555def case(
6556    expression: t.Optional[ExpOrStr] = None,
6557    **opts,
6558) -> Case:
6559    """
6560    Initialize a CASE statement.
6561
6562    Example:
6563        case().when("a = 1", "foo").else_("bar")
6564
6565    Args:
6566        expression: Optionally, the input expression (not all dialects support this)
6567        **opts: Extra keyword arguments for parsing `expression`
6568    """
6569    if expression is not None:
6570        this = maybe_parse(expression, **opts)
6571    else:
6572        this = None
6573    return Case(this=this, ifs=[])
6574
6575
6576def true() -> Boolean:
6577    """
6578    Returns a true Boolean expression.
6579    """
6580    return Boolean(this=True)
6581
6582
6583def false() -> Boolean:
6584    """
6585    Returns a false Boolean expression.
6586    """
6587    return Boolean(this=False)
6588
6589
6590def null() -> Null:
6591    """
6592    Returns a Null expression.
6593    """
6594    return Null()
6595
6596
6597# TODO: deprecate this
6598TRUE = Boolean(this=True)
6599FALSE = Boolean(this=False)
6600NULL = Null()